2

Is there a global command to delete/purge all disconnected .bdf files in an Oracle 11g R2 database?

Here is the scenario:

Every user-defined user and user-defined tablespace was deleted from an Oracle 11g R2 database by successfully executing the following commands for each and every user-defined user and tablespace: 

DROP USER username CASCADE;
DROP TABLESPACE tablespacename INCLUDING CONTENTS AND DATAFILES;

However, the folder containing the .dbf files corresponding with all the dropped tablespaces still contains all the same .dbf files, each with the same original file size. It is thus as if the data has simply been detached from the database without destroying the underlying datafiles.

I hesitate to simply delete all of the corresponding .dbf files out of fear of side-effects. So is there a global command in Oracle 11g R2 that will enable an administrator to safely delete all unused data files that were once connected with the Oracle 11g R2 instance?

I am using SQL Developer and SQLPlus, so any command or utility either of these tools could work.

Improve this question

1 Answer 1

Reset to default
4

Based on your description of this behaviour and your other comment, I guess your database runs on Windows.

The command you used should suffice and delete the datafiles. Except on Windows. On Windows, the datafiles are not always deleted, it is a known limitation.

Before dropping tablespaces or datafiles, take them offline:

alter tablespace ... offline;

or

alter database datafile ... offline drop;

And drop the datafile/tablespace after the above:

drop tablespace ... including contents and datafiles;

or

alter tablespace ... drop datafile ...;

If they still remain there, just simply delete them at OS level. And no, there is no such command thats deletes datafiles from the database that the database does not know of.

Reference:

Drop Tablespace Including Contents And Datafiles The Datafiles Are Not Automatically Deleted (Doc ID 389467.1)

Improve this answer
9
  • Thank you and +1 for taking the time to look into this. I have deleted the files as you suggested, given that the commands in the OP have already been run and failed to delete them. The .dbf files are thus now in the Windows recycle bin. Are there any other precautions that I should take to clean up Oracle 11g before trying to import more schema and data using other scripts? Also, is it safe to empty the windows recycle bin and thus permanently eliminate these .dbf files? I plan another data pump to replace all this from scratch, and want things clean first to avoid errors.
    – HereAndThere
    Commented Feb 27, 2016 at 19:12
  • I do not have an oracle support account, so I cannot read your link. But thank you for what you posted in your answer as best practices for avoiding similar problems in the future.
    – HereAndThere
    Commented Feb 27, 2016 at 19:13
  • @HereAndThere Yes, you can empty the recycle bin. I don't know what you plan to import, but some object types, like views, stored procedures, packages are not stored in user tablespaces, hence they are not dropped (they are stored in the dictionary, in the SYSTEM tablespace). If I were to re-import an existing schema, I would drop the schema first.
    – Balazs Papp
    Commented Feb 27, 2016 at 19:40
  • Thank you. How do I drop the schema? I imagine the steps are 1.) query all schemas, 2.) identify the ones to delete, then 3.) delete schemas by name. But how do I do that in Oracle 11g? What commands? If you think this is a separate question, I need help framing it so that I do not spam the ssite with poorly defined questions.
    – HereAndThere
    Commented Feb 27, 2016 at 19:47
  • Once you successfully issue a DROP TABLESPACE, any remaining file(s) from that tablespace has no more meaning to oracle than does a Word document. There are no leftover connections to the database. So nothing you do to it either directly or from the recycle bin has any meaning at all to oracle.
    – EdStevens
    Commented Feb 27, 2016 at 20:01

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.