How to remove the offline drop datafiles
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to remove the offline drop datafiles

Hybrid View

  1. #1
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159

    How to remove the offline drop datafiles

    One of our dba added a 3 datafile to a tablespace and he issued offline drop on all the datafiles he created on a noarchivelog mode
    DB.We tried to recreated the controlfile by avoiding this datafiles.
    Now when i select from the dba_datafiles it shows something like this

    /usr/oracle/product/8.1.7/dbs/MISSING00022
    AVAILABLE

    /usr/oracle/product/8.1.7/dbs/MISSING00023
    AVAILABLE

    /usr/oracle/product/8.1.7/dbs/MISSING00024
    AVAILABLE

    My DB is working perfectly fine but my export has a entry saying the tablespace related to this datafiles were offline.I checked the status of the tablespaces its online.

    Anyone know how to remove this entry permanently without any recreation of tablespace since this tablespace has lot of objects in it.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    er you cannot, that's not the way to remove datafiles, in fact you cannot remove datafiles! if you do that you have to drop the entire tablespace

    time to look for another proper dba

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    pando is right

    the fet$ table still references that datafile and at some point will try and allocate an extent from it, at which point you will be in big trouble,
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Try to drop objects located in those offline datafiles, then export all objects in tablespace mode.

    Recreate the tablespace, then import objects.

    or
    Try to drop objects located in those offline datafiles
    Switch database to archivelog mode
    Recover offline databfiles and bring them online
    Switch database back to noarchivelog mode
    Last edited by Calvin_Qiu; 09-10-2003 at 10:28 PM.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by Calvin_Qiu
    Try to drop objects located in those offline datafiles, then export all objects in tablespace mode.

    Recreate the tablespace, then import objects.

    or
    Try to drop objects located in those offline datafiles
    Switch database to archivelog mode
    Recover offline databfiles and bring them online
    Switch database back to noarchivelog mode
    I doubt you can even drop the objects...

    Regarding put in archive log, I dont see how that can help? You need ALL archived logs since datafile creation!

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