How to Manage Oracle Locks.

How to Manage Oracle Locks. 1) First find the sessions holding locks, and objects that are currently being locked. The following SQL prompts you for a schema/owner name and lists all currently lock objects and sessions locking them: select session_id, oracle_username locker, o.OBJECT_ID,object_name,object_type,process,DECODE (l.locked_mode , 0, ‘None’ , 1, ‘Null’ , 2, ‘Row-S’ , 3, ‘Row-X’ , 4, ‘Share’ , 5, ‘S/Row-X’ , 6, ‘Exclusive’) lock_mode from v$locked_object l,dba_objects o where l.object_id=o.object_id and o.owner like upper(‘&SCHEMANAME’) order by 2; Or use the following SQL if you already know the name of the object that is being locked and you want to know the locking session: select session_id, oracle_username locker, o.OBJECT_ID,object_name,object_type,process,DECODE (l.locked_mode , 0, ‘None’ , 1, ‘Null’ , 2, ‘Row-S’ , 3, ‘Row-X’ , 4, ‘Share’ , 5, ‘S/Row-X’ , 6, ‘Exclusive’) lock_mode from v$locked_object l,dba_objects o where l.object_id=o.object_id and o.owner like upper(‘&SCHEMANAME’) and o.object_name like upper(‘&object_name’) order by 2; 2) Lookup detail about the sessions holding locks. Now use the following SQL to see more detail about the locking session using session ID or SID you had listed from previous lock finding SQL: select s.sid,p.spid os_pid,s.status, s.osuser,s.username,s.COMMAND, s.MACHINE,s.MODULE,s.SERIAL#, s.SCHEMANAME, s.LOCKWAIT,s.action from v$session s, v$process p WHERE s.paddr = p.addr and s.sid = &SESSION_ID; 3) Lookup the session’s current SQL. Use the following SQL to the associated SQL using the SID you have found earilier: select sa.sql_text txt from v$session s, v$sqlarea sa where s.sql_address=sa.address(+) and s.sql_hashvalue=sa.hash_value(+) and s.sid = &SESSION_ID;