Created one dbf file from my database using following command,

alter tablesapce PAULA_INDEX add datafile '/u02/oradata/hfd1/index/index17.dbf' size 2000M;

This dbf file is created in my server locally /u02/oradata/hfd1/index/ path. And I am also able to view this file in my oracle database.

Now I want to delete this file from my database. And accidentally it was already removed locally from /u02/oradata/hfd1/index/ path. Now I am not able to drop the file from my database. Please suggest what I can do. I have tried,

alter tablespace PAULA_INDEX drop datafile '/u02/oradata/hfd1/index/index17.dbf';

But it is not dropped.

  • What was the error message when you tried to drop the file? It would be very rare to be able to drop an individual file from a multi-file tablespace, as all the files will have data. If, as the name suggests, this TS has only index segments, your best bet would be to drop the entire TS, re-create it, and rebuild the indexes. – EdStevens May 23 '16 at 23:43

You can try:

shutdown immediate
startup mount
alter database datafile '/u02/oradata/hfd1/index/index17.dbf' offline drop;
alter database open
  • This problem was related to my hfd1 database. If I give shutdown immediate then it will shut down my another two databases (emp1 and dms1). – pmaipmui May 23 '16 at 11:09
  • How do you think issuing a shutdown while connected to a database will shut down other databases? Is this the container DB of a 12c multi-tenent database? – EdStevens May 23 '16 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.