DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Database Objects & Datafiles

  1. #1
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588

    Question Database Objects & Datafiles

    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]

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    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

  3. #3
    Join Date
    Oct 2000
    Posts
    467
    selecting from the table with the rowid is another way..but thats just too complicated.
    Info by PIPO is the best and easiest.

    Vinit

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588

    Thumbs up Database Objects & Datafiles

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

    Thanks

    Sameer

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Database Objects & Datafiles

    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.




  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  7. #7
    Join Date
    Oct 2001
    Posts
    122

    Smile

    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 !





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width