I need to know objects in a perticular datafile. If I have a tablespace with 2 datafiles having a sufficient space empty. Then I would like to drop one of the datafile. For that I need to know if the datafile is empty or not...
I can findout the objects in tablespace.. But I like to see the objects in datafiles of that tablespace.
<----------------------- old post --------------------->
How do I find which database objects are stored in a datafile?
Pay attention to the fact that an object, say a table, may reside in two or more datafiles. It is the extent who is in a data file, not the object.
Importan to knowt: you are not able to specify the datafile location of the initial extent. Yu can only control the available space in the other datafiles relative to the size of the initial extent.
Oracle does not distinguish between datafiles, it looks at the total free space extents in the tablespace. Oracle will first look for a free extent equal to the one it needs to allocate. If such one does not exist, then Oracle breaks up an existing extent.