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
Max4rDBAAsked:
Who is Participating?
 
Max4rDBAConnect With a Mentor Author Commented:
Deleted datafile after dropping the tablespace
0
 
slightwv (䄆 Netminder) Commented:
Did you issue the offline drop command first?

ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\DATAP\DATAP_LARGE_12.DBF' OFFLINE DROP;
0
 
Max4rDBAAuthor Commented:
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.

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
slightwv (䄆 Netminder) Commented:
Check Metalink for some causes:

Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command [ID 1050261.1]
0
 
Max4rDBAAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
>> 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.
0
 
Max4rDBAAuthor Commented:
Found own answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.