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.
Best Answer
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)