Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    18

    Question Unanswered: datafile and objects

    I have to drop very large datafile, how can I view which Oracle objects uses this datafile?
    Murat SULUHAN
    Live Free or Die

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    DBA_EXTENTS should have what you need ... If you know the tablespace_name, then just select from dba_extents ... or if you know the
    specific data file name, file the file_id and use it ...

    Ex:

    select DISTINCT segment_name,segment_type from dba_extents where file_id = (select file_id
    from dba_data_files where FILE_name = 'D:\ORADATA\DATAFILES\OPER1.ORA')


    HTH
    Gregg

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    Use DBA_SEGMENTS instead - it's faster with less overhead, and you don't need the DISTINCT. DBA_EXTENTS is actually a view built on top of dba_segments.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Whoops. shouldn't be here - remember everyone, I just got home from the pub.

    Just deleted all sorts of reply about object dependencies - then realised it was about dropping a datafile :-)

    Sorry, I'll go and sit in the corner!

    Cheers
    Bill
    Last edited by billm; 06-08-04 at 20:01.
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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