Oracle Lock table and wait

/*oracle user Lock, database lock sometimes compared to eat resources, if a large number of resources consumed, then other applications will not have the resources to use, it will be abnormally closed or unable to open. When Oracle has a lock waiting, it must find the waiting lock, if necessary, find the corresponding session and kill it. */

"Kill the Conversation."

> alter system kill session ' sid,serial '--This is the time to find Sid and Serial> select Sid,serial from V$session in V$session

SELECT/*+ Rule */S.username,decode (l.type, ' TM ', ' TABLE lock ',              ' TX ', ' ROW lock ',              NULL) Lock_level,o.owner, O.object_name,o.object_type,s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuserfrom v$session S,v$lock L,dba_ Objects Owhere L.sid = S.sidand L.id1 = o.object_id (+) and s.username are not NULL---ERP----------------------------------E Rpselect Lower (SUBSTR (all_objects.owner| | object_name,1,16)),                       SUBSTR (os_user_name,1,10),                       v$locked_object.process,                       SUBSTR (terminal,1,7),                       v$session.sid,v$session.serial#,                       v$session.machine from                    v$locked_object,all_objects,v$session                   WHERE v$locked_object.object_id=all_objects.object_id and                     V$locked_object. Session_id=v$session. SID----ERP----------------------------------ERP

/* If there is a lock waiting, you need to find out who caused the wait */

SELECT/*+ Rule */Lpad (", Decode (L.xidusn, 0,3,0)) | | L.oracle_username user_name,       o.owner,o.object_name,o.object_type,s.sid,s.serial#from v$locked_object L,dba_ Objects o,v$session swhere l.object_id=o.object_idand l.session_id=s.sidorder by o.object_id,xidusn desc Above query result is a tree-like structure If there is a child node, it indicates that there is a wait to occur. If you want to know which rollback segment the lock uses, you can also associate to V$rollname, where Xidusn is the USN of the rollback segment

Oracle Lock table and wait