in which datafile object resides?
Posted by Pavan DBA on April 23, 2010
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>;
krishnaprasad said
hi pavan what about the extent allocation in oracle 11g.
Pavan DBA said
hi krishna, it is same in 11g also.
sorry culdn’t reply u bcz i am on leave since 3 weeks
Martin said
Hello,
does this mean that simply spoken if a table has only one extent, it will stay within one datafile. if it has two extents, it will spread across two datafiles, 3 extents -> 3 datafiles and so on?
Pavan Kumar said
Hi Martin, yes. but that also depends on type of tablespace (local/dictionary) and a discussion is going on whether this round robin allocation is continuing in 10g also (actually it was till 9i)