2

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.

Improve this question
1
  • 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
    Commented May 23, 2016 at 23:43

1 Answer 1

Reset to default
2

You can try:

shutdown immediate
startup mount
alter database datafile '/u02/oradata/hfd1/index/index17.dbf' offline drop;
alter database open
Improve this answer
2
  • 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).
    – Nainita
    Commented May 23, 2016 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
    Commented May 23, 2016 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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