DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13
  1. #11
    Join Date
    Jun 2000
    Madrid, Spain
    xyz I suggest you to read Note:111316.1 from Metalink, the Note states the plain reasons but there are further internals problems to delete a datafile from a tablespace

  2. #12
    Join Date
    Jun 2001
    When you 'drop' a datafile, it is not removed from the data dictionary. The key issue is that the file is still referenced in sys.fet$ (free extent table). When a segment attempts to allocate a next extent, it may try to use the datafile that no longer exists. This will cause the invoking transaction to fail. By forcing oracle to skip over the file (as per the metalink article), you insure that this situation will never be encountered.

    If you do this, make certain to document the reasons. Better yet, take the next opportunity to drop and recreate the tablespace. Of course, if it is the system tablespace, you are going to have to drop and recreate the entire database.

    Daniel W. Fink

  3. #13
    Join Date
    Oct 2000
    Saskatoon, SK, Canada
    Pando is correct.

    Extract from that NOTE
    The ALTER DATABASE DATAFILE  OFFLINE DROP command, is not meant 
    to allow you to remove a datafile. What the command really means is that you 
    are offlining the datafile with the intention of dropping the tablespace.
    If you are running in archivelog mode, you can also use:
    instead of OFFLINE DROP.  Once the datafile is offline, Oracle no longer 
    attempts to access it, but it is still considered part of that tablespace. This
    datafile is marked only as offline in the controlfile and there is no SCN 
    comparison done between the controlfile and the datafile during startup (This 
    also allows you to startup a database with a non-critical datafile missing).  
    The entry for that datafile is not deleted from the controlfile to give us the
    opportunity to recover that datafile.
    The best solution would be using export on tablesapce, then drop the tablespace and then re create the tablespace and do the import on it.


    Life is a journey, not a destination!

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