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 tablespaceSQL>ALTER DATABASE DATAFILE '<file_name>' OFFLINE DROP;
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)