Pavan DBA's Blog

The DBA Knowledge Store

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>;

4 Responses to “in which datafile object resides?”

  1. krishnaprasad said

    hi pavan what about the extent allocation in oracle 11g.

  2. 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?

    • 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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: