Database Objects & Datafiles

[Sameer]

Hi..

I reframe the question again..

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.

Thanks

Sameer


<----------------------- old post --------------------->

Hello,

How do I find which database objects are stored in a datafile?

Thanks

Sameer



[Edited by Sameer on 10-15-2001 at 04:45 AM]

[pipo]

if you join SYS.DBA_EXTENTS and SYS.DBA_DATA_FILES on the FILE_ID, then you can know which segment is in a precise datafile

[vinit]

selecting from the table with the rowid is another way..but thats just too complicated.
Info by PIPO is the best and easiest.

[Sameer]

Thanks a lot PIPO.. I got the result..

Thanks

Sameer

[julian]

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.

[Shestakov]

You can't drop any datafile in tablespace.
If you want it you must reorganized (recreate) whole tablespace.

To julian.
In my mind oracle search next free extent using strategy like mini-max.
Oracle try to find minimum extent that has size equal or more large then INITIAL.

[prafful]

Your best bid is to reorganize the tablespace for following reasons

1. An Object(segment) has multiple extents residing at more then one datafile. Logically Object has realation with tablespace but not with datafile.

2. Oracle doesn't support dropping datafiles ! So even though
you manage to empty a datafile, you can not drop it.

Therefore, it is a good idea to recreate the tablespace
with only needed space. (I guess you want to free up the space) Explore if you can shrink the datafile !