ORACLE Database | remove tablespace with missing datafile

Question

I mistakenly remove the datafiles before I drop the tablespace. But the tablespace occupy a large size space. I need to remove it, any method?

It occur:

DROP TABLESPACE abc;

*

ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/data/oradata/oracle/abc.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Answer 1

If your datafile is held inside a PDB.
You will have to follow the next commands :

SHUTDOWN ABORT
STARTUP
ALTER PLUGGABLE DATABASE $MyPDB OPEN;

On this last command it should fail with an ORA-01110 error.
And if you try ALTER DATABASE DATAFILE $datafileNumber OFFLINE DROP;
You will encounter an ORA-01516.

This is because you are trying to DROP a datafile on the CDB instead of the PDB.

To do this properly, you have to modify the session to target PDB :

ALTER SESSION SET CONTAINER=$MyPDB;

Now you can drop the datafile and open the database :

ALTER DATABASE DATAFILE $datafileNumber OFFLINE DROP;
ALTER PLUGGABLE DATABASE $MyPDB OPEN;

References

https://blogs.oracle.com/robertgfreeman/pdb-recovery-your-pdb-wont-open-because-a-datafile-is-missing