Ran into following issue were accidentally the datafile was removed before dropping the tablespace. If not fixed this can cause the instance to crash.

The alert log had the following message in this case, it shows the instance cannot access the datafile:
…
Errors in file /orabin/diag/rdbms/testdb/TESTDB/trace/TESTDB_m000_64749810.trc:
ORA-01116: error in opening database file 89
ORA-01110: data file 89: ‘/oradata/TESTDB/tbs01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory

If the issue is not fixed the instance crashed on it’s own with following message after some time:
…
Tue Nov 22 17:17:01 2011
System state dump requested by (instance=1, osid=9044114 (CKPT)), summary=[abnormal instance termination].
System State dumped to trace file /orabin/diag/rdbms/testdb/TESTDB/trace/TESTDB_diag_35586096.trc
Instance terminated by CKPT, pid = 9044114
Tue Nov 22 18:43:13 2011

The tablespace can’t be dropped or datafile can’t be taken offline as in this case the instance was running in NOARCHIVE log mode. so one would need to use the “OFFLINE DROP” option show below

SQL> ALTER DATABASE DATAFILE ‘/oradata/TESTDB/tbs01.dbf’ OFFLINE;
ALTER DATABASE DATAFILE ‘/oradata/TESTDB/tbs01.dbf’ OFFLINE
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

SQL> ALTER DATABASE DATAFILE ‘/oradata/TESTDB/tbs01.dbf’ OFFLINE DROP;

Database altered.

— Step to show the datafile cannot be made online unless datafile is recovered
SQL> ALTER DATABASE DATAFILE ‘/oradata/TESTDB/tbs01.dbf’ ONLINE;
ALTER DATABASE DATAFILE ‘/oradata/TESTDB/tbs01.dbf’ ONLINE
*
ERROR at line 1:
ORA-01113: file 89 needs media recovery
ORA-01110: data file 89: ‘/oradata/TESTDB/tbs01.dbf’

— Display the online status of the datafile
SQL> select * FROM dba_data_files where tablespace_name = ‘TESTTBS’;

FILE_NAME
——————————————————————————–
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
———- —————————— ———- ———- ———
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
———— — ———- ———- ———— ———- ———–
ONLINE_
——-
/oradata/TESTDB/tbs01.dbf
89 TESTTBS AVAILABLE
89
RECOVER

SQL> drop tablespace TESTTBS;

Tablespace dropped.

SQL> select * from dba_data_files where file_name like ‘/oradata/TESTDB/tbs01.dbf’;

no rows selected

— Confirm the datafile is no longer there by creating a control file to trace
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE as ‘/tmp/test.ctl’;

In the scenerio if the database crashed one would need start the database in mount state. If you try to offline the datafile with using the “OFFLINE DROP” option again in this case you will receive the following “ORA-01145: offline immediate disallowed unless media recovery enabled” as this is allowed when the database is in ARCHIVE log mode.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 521936896 bytes
Fixed Size 2177328 bytes
Variable Size 314574544 bytes
Database Buffers 197132288 bytes
Redo Buffers 8052736 bytes
Database mounted.

— offline drop the datafile before opening the database, the database can’t be opened as the file doesn’t exists and control file will look for the datafile when one opens the instane.
SQL> ALTER DATABASE DATAFILE ‘/oradata/TESTDB/tbs01.dbf’ OFFLINE DROP;

Database altered.

SQL> alter database open;

Database altered.

— Shows the online status of the datafile in dba_data_files
SQL> select * from dba_data_files where file_name like ‘/oradata/TESTDB/tbs01.dbf’;

FILE_NAME
——————————————————————————–
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
———- —————————— ———- ———- ———
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
———— — ———- ———- ———— ———- ———–
ONLINE_
——-
/oradata/TESTDB/tbs01.dbf
89 TESTTBS AVAILABLE
89
RECOVER

SQL> drop tablespace TESTDBS;

Tablespace dropped.

SQL> select * from dba_data_files where file_name like ‘/oradata/TESTDB/tbs01.dbf’;

no rows selected

— Confirm the datafile is no longer there by creating a control file to trace
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE as ‘/tmp/test.ctl’;

Database altered.

If the instance was running in ARCHIVE LOG mode, the datafile can be offline and tablespace can be dropped as shown below
SQL> ALTER DATABASE DATAFILE ‘I:\DB\ORACLE\TESTDB\ORADATA\TBS01.dbf’ OFFLINE;

Database altered.

— Shows the online status of the datafile in dba_data_files
SQL> select * from dba_data_files where file_name like ‘/oradata/TESTDB/tbs01.dbf’;

FILE_NAME
——————————————————————————–
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
———- —————————— ———- ———- ———
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
———— — ———- ———- ———— ———- ———–
ONLINE_
——-
/oradata/TESTDB/tbs01.dbf
89 TESTTBS AVAILABLE
89
RECOVER

SQL> drop tablespace TESTTBS;

Tablespace dropped.