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
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
Pando is correct.
Extract from that NOTE
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.
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.
Life is a journey, not a destination!
Click Here to Expand Forum to Full Width