The ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command is not meant to allow you to remove a datafile. What the command really means is that you are off lining the datafile with the intention of dropping the tablespace.
If you are running in archive log mode, you can also use:
ALTER DATABASE DATAFILE <datafile name> 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 control file and there is no SCN comparison done between the control file 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 control file to give us the opportunity to recover that datafile.
Leave a Reply