The Oracle database can’t be relied on to remove datafiles when you drop a tablespace (including contents and datafiles) on Windows, even with Oracle 12.1 (most recent version at time of writing).

I discovered this annoying bug when trying to clean up after some experiments in a sandpit database.

11:05:35 SYS@DB12c SQL> drop tablespace TEST_TBS including contents and datafiles;

Tablespace dropped.

No warnings were reported in my session, but the alert log showed:

Wed Jan 04 11:05:57 2017
WARNING: Cannot delete file D:\ORADATA\DB12C\TEST_TBS01.DBF
Wed Jan 04 11:05:57 2017
Errors in file C:\ORA\diag\rdbms\db12c\db12c\trace\db12c_ora_18376.trc:
ORA-01265: Unable to delete DATA D:\ORADATA\DB12C\TEST_TBS01.DBF
ORA-27056: could not delete file
OSD-04024: Unable to delete file.
O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.
Completed: drop tablespace TEST_TBS including contents and datafiles

 

The drop tablespace .. including contents and datafiles command will work if all sessions that had accessed segments in that datafile (and therefore threads with file locks) are closed first.  (Possibly closing the cursors will be sufficient).

The other solution Oracle suggested didn’t work for me in Oracle 12.1:

Issue this command before dropping the tablespace

SQL>ALTER DATABASE DATAFILE '<file_name>' OFFLINE DROP;

Refer to:
Bug 2338968 : DROP TABLESPACE DOES NOT ALWAYS AUTOMATICALLY DROP DATAFILES.
Drop Tablespace Including Contents And Datafiles The Datafiles Are Not Automatically Deleted (Doc ID 389467.1)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s