How to find in which datafile object resides

Lets say i created a new tablespace with 8 datafiles.

SQL> select file_name from dba_data_files where tablespace_name=’TESTNDEX’;

FILE_NAME
——————————————————————————–
/data1/oradata/TESTINDEX01.dbf
/data1/oradata/TESTINDEX02.dbf
/data1/oradata/TESTINDEX03.dbf
/data1/oradata/TESTINDEX04.dbf
/data1/oradata/TESTINDEX05.dbf
/data1/oradata/TESTINDEX06.dbf
/data1/oradata/TESTINDEX07.dbf
/data1/oradata/TESTINDEX08.dbf

8 rows selected.

i created a table called TEST in this tablespace.

now many people think that space will be allocated (or extents will be allocated) from 1st datafile of this tablespace and once it is full, it will use 2nd, 3rd etc….which is not the real picture

the fact is, if i create a table, when i start inserting data into that, extents will be allocated from all the datafiles in ROUND ROBIN fashion (if you don’t know about this, just do a google !)

So, in our example it will go to all the 8 datafiles

so based on the allocation of extents and size of data, object can reside in all the datafiles or some datafiles.

How can we check in which datafile my object resides?

select a.segment_name,a.file_id,b.file_name Datafile_name from dba_extents a,dba_data_files b where a.file_id=b.file_id and a.segment_name=’YOUR OBJECT NAME’;

How can we check what objects are there in a datafile?

select a.segment_name,a.file_id,b.file_name Datafile_name from dba_extents a,dba_data_files b where a.file_id=b.file_id and b.file_name=<your datafile name with path>;