Best query for oracle database Locks checking

Recently we ran into an error Oracle error which says

Caused By: Error executing SQL ALTER TABLE ***_ALLOCATION ADD REGION_ID NUMBER(38):         

Caused By: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The most common reason for this are either 'SELECT FOR UPDATE ' or some uncommitted INSERT statements.

Combining the information I got from several Google searches, I ended up with this sql

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,SQ.SQL_FULLTEXT, S.LOGON_TIME FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;

This gave me info about the sql and the table which it has locked and the logon time.

OBJECT_NAME	SID	SERIAL#	SPID	PROGRAM	LOGON_TIME	SQL_FULLTEXT
TABLE_USER	953	40807	9179	JDBC Thin Client	26-Jul-12	INSERT INTO TABLE_USER VALUES (BLAH…BLAH)
TABLE _USER	953	40807	9179	JDBC Thin Client	26-Jul-12	INSERT INTO TABLE_USER VALUES (BLAH…BLAH)
TABLE _USER	953	40807	9179	JDBC Thin Client	26-Jul-12	INSERT INTO TABLE_USER VALUES (BLAH…BLAH)

The info on the user name and the machine name can also be obtained from the table V$SESSION.  Just add S.USERNAME and S.MACHINE to the above sql.

We have 3 options to fix this error

1.      Kill the DB session and get the tables unlocked
2.      Kill the application which holds this particular session(sql connection)
3.      The ideal solution is to get to the actual process(application) to debug/fix the issue

1. Killing the DB session

To kill the DB session execute the  sql

alter system kill session 'sid,serial#'

In my case it will be

               alter system kill session '953,40807'

2. Killing the Application which holds the session

Get the column value of MACHINE from the table V$SESSION and search for the running processes in that machine.

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE,S.PORT ,S.LOGON_TIME,SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;

In my case I just had a look and I knew who the culprit was from the LOGON_TIME from the sql results

[bash]$ ps aux | grep java

user 30745  0.0  3.3 677336 63172 ?     Sl   Jul26   3:26 /usr/local/jdk1.6.0_14/jre/bin/java -jar /opt/ea/nova/jenkins/jobs/project/workspace/target/surefire/surefirebooter5649151821229923783.jar /opt/ea/nova/jenkins/jobs/ project /workspace/target/surefire/surefire1933027872679766101tmp /opt/ea/nova/jenkins/jobs/ project/workspace/target/surefire/surefire5320833548219373656tmp

Now that we got to the process, we can easily debug the problem (or just kill the process :P) I did a

   kill -15 30745

and puff!!!! Problem solved!!!! Yay!!

3. Getting to the Actual Process on the application server

The better way to find the process is to get the V$SESSION.PORT from the above sql  and find the process listening on that port.

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE,S.PORT ,S.LOGON_TIME,SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;

To get to the process listening on the port, execute the below command.For me, the port number was  34465

  netstat -ap | grep 34465

which gave me

tcp     0   0 machine.name:34465  oracle.db.com:ncube-lm ESTABLISHED 4030/java

Bingo!!! We got the process id 4030. Debug or Kill; Your Choice!!!

Thanks,

Reef: http://abeytom.blogspot.com/2012/08/finding-and-fixing-ora-00054-resource.html