How to drop Tablespace when datafile does not exist

Document created by zgofa01 Employee on Aug 31, 2015
Version 1Show Document
  • View in full screen mode

HOW CAN I DROP TABLESPACE IF A DATAFILE IS MISSING

1) I dropped the idashdata.dbf from /u01/app/oracle/oradata/XE directory

2) I have this kind of error to remove the CA Workload Automation iDash database in Oracle,

SQL> drop tablespace IDASHDATA including contents and datafiles cascade constraints;

 

drop tablespace IDASHDATA including contents and datafiles

*

ERROR at line 1:

ORA-01116: error in opening database file ..

ORA-01110: data file .. : '/u01/app/oracle/oradata/XE/idashdata.dbf': unable to open file ..

No such file or directory

 

THE SOLUTION

 

SQL> alter database datafile '/u01/app/oracle/oradata/XE/idashdata.dbf' offline drop;

SQL> drop tablespace  IDASHDATA including contents cascade constraints;

Attachments

    Outcomes