asked on

Datafile deletion

Hello All,

I am getting "ORA-00060: deadlock detected while waiting for resource" while deleting a datafile.

when i checked in dba_data_files and V$datafile the ONLINE_STATUS/STATUS is in RECOVER mode.

I do not need this datafile so i tried to drop using the following command ..

ALTER TABLESPACE DATAP_LARGE DROP DATAFILE 'D:\ORACLE\ORADATA\DATAP\DATAP_LARGE_12.DBF';

I checked the Trace file geenrated for the deadlock, the Session which is in question is the session where i ran the command (SQLPLUS).

Can anybody throw some light on this.

Thanks

Rahul

slightwv (䄆 Netminder)

Did you issue the offline drop command first?

ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\DATAP\DATAP_LARGE_12.DBF' OFFLINE DROP;

Max4rDBA

ASKER

Yes Slightwv , i have executed the offline drop command and then i executed the "ALTER TABLESPACE DATAP_LARGE DROP DATAFILE 'D:\ORACLE\ORADATA\DATAP\DATAP_LARGE_12.DBF'; " where i got the deadlock error.

slightwv (䄆 Netminder)

Check Metalink for some causes:

Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command [ID 1050261.1]

Max4rDBA

ASKER

Thanks Slightvw, I saw the metalink just before migrating the tablespace i wanted to know if i put the database in the MOUNT stage and then execute the command will the drop datafile work.

Thanks

Rahul

slightwv (䄆 Netminder)

>> i wanted to know if i put the database in the MOUNT stage and then execute the command will the drop datafile work.

I have no idea. If you are able to shutdown the database it's worth a try.

ASKER CERTIFIED SOLUTION

Max4rDBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Max4rDBA

ASKER

Found own answer