Here I take advantage of Statspack's data to retrieve valuable information of IO on data files.
# sqlplus "/ as sysdba"
SQL> select sp.tsname tablespace_name, sp.filename, df.bytes/1024/1024/1024 size_gb, sum(sp.phyrds) total_read, sum(sp.phywrts) total_write, sum(sp.phyrds) + sum(sp.phywrts) total_read_write, (sum(sp.phyrds) + sum(sp.phywrts)) / (df.bytes/1024/1024/1024) total_read_write_per_gb from perfstat.stats$filestatxs sp inner join sys.dba_data_files df on df.file_name = sp.filename where sp.snap_id between 2001 and 2200 group by sp.tsname, sp.filename, df.bytes/1024/1024/1024 order by 7 desc;
In the statement, I sort the top IO (read + write) traffic on data files per GB as the result, it can make me know the best candidates of data file in the database. Oppositely, I can also identify the coldest data files for moving them to those archive disks with slower transfer rate by sorting them reversely.
Before applying the above statement for your database, you have to replace SNAP_ID (i.e. 2001 to 2200 in this case) into yours.