Tips for resolving Oracle Locks

Some of the performance of the alarm information through the Dba_outstanding_alerts table can be found, the locked table if the long-term release, here can also find the relevant session of the SID and Serial#.

Some of the performance of the alarm information through the Dba_outstanding_alerts table can be found, the locked table if the long-term release, here can also find the relevant session of the SID and Serial#.

SELECT * FROM Dba_outstanding_alerts

The V$locked_object view can see the currently locked objects, only those sessions that have been present can be locked, so it is not easy to jump to conclusions, especially in a RAC environment, you must see each node to see where the relevant session is.

SELECT * FROM V$locked_object

Sometimes you can also view the lock information based on the object name:

Select Sid,id1,type from V$lock where id1= (select object_id from dba_objects where Object_name=upper (' mytablename '));

Found the session that caused the lock, you can find the corresponding SID and Serial#

Select Saddr,sid,serial#,paddr,username,status from V$session where sid=772

So you can kill this session:

Sql> alter system kill session ' 772,9044 ';

System altered.

You can query the association between the processes of the Oracle database and the operating system processes:

Select spid, Osuser, s.program from V$session s,v$process p

where S.paddr=p.addr and s.sid=772

Sometimes some process to kill in the operating system, then can use the kill-9 PID command:

[Root@erpdevdb ~]# Ps-efgrep Sqlplus

Oracle 11847 11126 0 16:39 pts/1 00:00:00 sqlplus-s @/oracle/home/droptable.sql

Root 11889 11856 0 16:40 pts/2 00:00:00 grep sqlplus

[Root@erpdevdb ~]# kill-9 11847

Blocking caused by database object locking is a tricky business and must be handled with caution.