I faced with those errors on 12c database. While I try to drop some tablespace it throws errors such as:

SQL> DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE MY_TABLESPACEINCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-01116: error in opening database file 35
ORA-01110: data file 35:
'/u01/app/oracle/product/12.1.0/dbhome_2/dbs/MY_TABLESPACE02_A.DBF'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

Even I try to take tablespace to offline I hit the same errors

SQL> alter tablespace MY_TABLESPACE offline;
alter tablespace MY_TABLESPACE offline
*
ERROR at line 1:
ORA-01116: error in opening database file 35
ORA-01110: data file 35:
'/u01/app/oracle/product/12.1.0/dbhome_2/dbs/MY_TABLESPACE02_A.DBF'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

So here is the solution

SQL> alter database datafile '/u01/app/oracle/product/12.1.0/dbhome_2/dbs/MY_TABLESPACE02_A.DBF' offline drop;

Database altered.

SQL> DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Advertisements