drop tablespace did not delete a file from a disk

OS: Windows 2003 Enterprise (Service Pack 2)

Oracle: 10.2.0.4

I encountered a problem during dropping tablespace.

I had the empty tablespace RMAN_DATA_TEMP with one datafile.

TABLESPACE_NAME	FILE_NAME	Size MB	Free MB
RMAN_DATA_TEMP	D:\ORACLE\ORADATA\XXX\RMAN_DATA_TEMP.DBF	2700	2700

sys@XXX.world> drop tablespace RMAN_DATA_TEMP including contents and datafiles; Tablespace dropped.

It is good habit to look into the alert log after this kind of operation.

Thu May 05 08:07:10 2011 drop tablespace RMAN_DATA_TEMP including contents and datafiles Thu May 05 08:07:12 2011 WARNING: Cannot delete file D:\ORACLE\ORADATA\XXX\RMAN_DATA_TEMP.DBF Thu May 05 08:07:12 2011 Errors in file d:\app\oracle\admin\XXX\udump\XXX_ora_5852.trc: ORA-01265: Unable to delete DATA D:\ORACLE\ORADATA\XXX\RMAN_DATA_TEMP.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.

The tablespace RMAN_DATA_TEMP was deleted from the database dictionary but the datafile was not deleted from the disk.

Let`s see the trace file d:\app\oracle\admin\XXX\udump\XXX_ora_5852.trc.

Dump file d:\app\oracle\admin\XXX\udump\XXX_ora_5852.trc Thu May 05 08:07:12 2011 ORACLE V10.2.0.4.0 - Production vsnsta=0 vsnsql=14 vsnxtr=3 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine and Real Application Testing options Windows Server 2003 Version V5.2 Service Pack 2 CPU                 : 4 - type 586, 2 Physical Cores Process Affinity    : 0x00000000 Memory (Avail/Total): Ph:83M/2047M, Ph+PgF:2293M/5084M, VA:1031M/3071M Instance name: XXX Redo thread mounted by this instance: 1 Oracle process number: 28 Windows thread id: 5852, image: ORACLE.EXE (SHAD) *** 2011-05-05 08:07:12.266 *** ACTION NAME:() 2011-05-05 08:07:12.188 *** MODULE NAME:(sqlplus.exe) 2011-05-05 08:07:12.188 *** SERVICE NAME:(SYS$USERS) 2011-05-05 08:07:12.188 *** SESSION ID:(299.29437) 2011-05-05 08:07:12.188 ORA-01265: Unable to delete DATA D:\ORACLE\ORADATA\XXX\RMAN_DATA_TEMP.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.

Unfortunatelly in the trace file there are no additional information.

On the Metalink I found the note

Drop Tablespace including contents and datafiles the datafiles are not automatically deleted [ID 389467.1]

Solution

To implement the solution, please execute the following steps:

* Try to end/exit any other sql sessions that might have opened the files in the tablespace before dropping
the tablespace.
* Issue this command before dropping the tablespace
SQL>ALTER DATABASE DATAFILE '' OFFLINE DROP;

So, let`s try

sys@XXX.world> alter database datafile 'D:\ORACLE\ORADATA\XXX\RMAN_DATA_TEMP.DBF' offline drop; alter database datafile 'D:\ORACLE\ORADATA\XXX\RMAN_DATA_TEMP.DBF' offline drop; * ERROR at line 1: ORA-01516: nonexistent log file, datafile, or tempfile "D:\ORACLE\ORADATA\XXX\RMAN_DATA_TEMP.DBF"

Ups, it`s not working.

If the datafile still exist on a disk I will try to reuse it.

sys@XXX.world> create tablespace RMAN_DATA_TEMP datafile 'D:\oracle\oradata\XXX\RMAN_DATA_TEMP.DBF' size 10M reuse; Tablespace created.

So, I came back to the game.

Let`s try to delete one again .

sys@XXX.world> drop tablespace RMAN_DATA_TEMP including contents and datafiles; Tablespace dropped.

And let`s have a look into the alert log.

Thu May 05 08:30:21 2011 drop tablespace RMAN_DATA_TEMP including contents and datafiles Thu May 05 08:30:21 2011 Deleted file D:\ORACLE\ORADATA\XXX\RMAN_DATA_TEMP.DBF

Success. Sometimes the Metalink solution does not work.