deleting datafile - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: deleting datafile

  1. #11
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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
    Posts
    29
    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
    oracledba@ix.netcom.com

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

    Extract from that NOTE
    Code:
     
    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:
    
        ALTER DATABASE DATAFILE  OFFLINE; 
    
    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.

    Sam
    Thanx
    Sam



    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