This morning I did a mistake, I made a datafile offline by using

alter tablespace users drop datafile 'C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_USERS_6LD56LRR_.DBF';

The reason is I want to drop that datafile. After that I add one more datafile to that tablespace n try to delete the offline datafile. But I got the error:

deadlock found while waiting for resource.

Can anyone help me to fixed this so that I can drop that datafile safely.


This error is produced for various reasons stated below:

Too high activity - Re-running the job during a less busy period can fix this ORA-00060 deadlock error. Poor application design - As noted in the trace file: "The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL."

ASSM - This error can happen when the target tablespace is using "segment space management auto". See these notes on deadlocks with ASSM. Beware, ASSM can cause huge deadlocks, because ASSM does not perform well under heavy DML loads.

RAM shortage - In rare cases this ORA-00060 deadlock error can happen with a RAM shortage at the Java layer or Oracle layer

One way is to use the trace file to see what is causing the deadlock.

The most common reason for which such deadlock situation occurs is that some resource(s) might be using the datafile that you want to drop.

The best possible solution is to close/terminate all the sessions using the resources on the mentioned datafile and then delete the datafile.

Another solution is by shutting down the database and then making the necessary changes.

There are many ways/queries to find the SID(Session ID) which is causing the lock. Please refer the website for some examples -->http://www.oraclerecipes.com/monitoring/find-blocking-sessions/

Analogy in Windows: It is similar to renaming/deleting a file when in use. Hope this solves your problem !!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.