View the Oracle deadlock process and end the deadlock

View the lock table process SQL statement 1:SelectSess.sid, sess.serial#, Lo.oracle_username, Lo.os_user_name, AO.object_name, Lo.locked_mode fromv$locked_object Lo, dba_objects ao, v$session sesswhereAo.object_id =Lo.object_id  andlo.session_id=sess.sid; Viewing the lock table process SQL Statement 2:Select *  fromv$session T1, V$locked_object T2whereT1.sid=T2. session_id; Kill the Lock table process: If there is a record that says lock, record the SID and Serial#, replace the ID with the following 738,1429, you can remove the lockAlterSystemKillSession'738,1429'; Use this to check:SelectS.sid,s.machine,o.object_name, L.oracle_username,l.locked_mode,'ALTER SYSTEM KILL SESSION" "||S.sid||',  '||s.serial#||" ";'Command fromV$locked_object l,v$session s,all_objects owherel.session_id=S.sid andL.object_id=O.object_idcan see which machine which user locked the record, where command is used to kill the locked record session********************************************************************************************************** ******** SELECTA.object_idB.object_name, a.session_id, A.oracle_username, A.os_user_name, a.process, A.locked_mode fromV$locked_object A, Dba_objects BWHEREA.object_id =B.object_id; SELECTT2. USERNAME, T2. SID, T2. serial#, T2. Logon_time fromV$locked_object T1, V$session T2WHERET1. session_id=T2. SidORDER  byT2. Logon_time; ALTERSYSTEMKILLSESSION'SID, Serial#'; ********************************************************************************************************** **********Session1: \ C>Sqlplus HXG/hxg SQL> Select *  fromscott.t; A B C--------------------------------------------------111 aa bb 222 Hello WorldSql> Updatescott.tSetB='Good' whereA=222; updated1line. Session2: \ C>Sqlplus Scott/Tiger SQL> Select *  fromscott.t; A B C--------------------------------------------------111 aa bb 222 Hello WorldSql> UpdateTSetB='Asdfds' whereA=222; hang up .... Session3: \ C>Sqlplus "System/** * as SYSDBA "sql> select Sid,serial#,username,status from V$session;         SID serial# USERNAME STATUS----------------------------------------------------146                            SYS ACTIVE 147 one SYS INACTIVE 148 2 Hxg                                INACTIVE 5 SCOTT ACTIVE 151 1 Active 154 1 Active 159 6 ACTI          VE 1 Active 161 1 Active 162                       1 Active 163 1 active SID serial# USERNAME                                STATUS----------------------------------------------------164 1  Active 165 1 Active166 1 Active 167 1 active 168 1                                Active 169 1 Active 170 1 ACTIVE has selected 18 rows. Sql> alter system kill session ' 148,2 '; The system has changed.  Sql> select Sid,serial#,username,status from V$session;         SID serial# USERNAME STATUS----------------------------------------------------146                            SYS ACTIVE 147 one SYS INACTIVE 148 2 Hxg                                Killed 5 SCOTT INACTIVE 151 1 Active 154 1 Active 159 6 ACTI          VE 1 Active 161 1 Active 1621 Active 163 1 active SID serial# USERNAME                                STATUS----------------------------------------------------164 1  Active 165 1 Active 166 1 active          167 1 Active 168 1 Active 169 1 Active 1 Active has selected 18 rows. Sql> Select V$session.sid,v$session.   Serial#,v$process.spid, RTrim (object_type) Object_type,rtrim (owner) | |   ‘.‘   || object_name object_name, decode (lmode, 0, ' None ', 1, ' Null ', 2, ' Row-s ', 3, ' Row-x ', 4   , ' Share ', 5, ' S/row-x ', 6, ' Exclusive ', ' Unknown ') Lockmode, decode (request, 0, ' None ', 1, ' Null ', 2, ' Row-s ', 3,  ' Row-x ', 4, ' Share ', 5, ' S/row-x ', 6, ' Exclusive ', ' Unknown ') Requestmode, CTime, Block B, V$session.username,machine,module,action, Decode (a.type, ' MR ', ' Media Recovery ', ' RT ', ' Redo Thread ' , ' UN ', ' User Name ', ' TX ', ' Transaction ', ' TM ', ' DML ', ' UL ', ' PL/SQL User Lock ', ' DX ', ' D   istributed xaction ', ' CF ', ' Control file ', ' is ', ' Instance state ', ' FS ', ' File Set ', ' IR ', ' Instance Recovery ', ' ST ', ' Disk Space Transaction ', ' TS ', ' Temp Segment ', ' IV ', ' Library Cac '     He invalida-tion ', ' LS ', ' Log Start or Switch ', ' RW ', ' Row Wait ', ' SQ ', ' Sequence number ',    ' TE ', ' Extend table ', ' TT ', ' Temp table ', ' Unknown ') LockType from (SELECT * from V$lock)   A, all_objects,v$session,v$process where A.sid > 6 and object_name<> ' obj$ ' and A.ID1 = All_objects.object_ID and A.sid=v$session.sid and v$process.addr=v$session.paddr; The same is done by writing SQL from the data dictionary. SELECT-SQL1//function: Check for locked object//select obj. owner| | '. ' | | Obj. object_name as Obj_name,//object name (already locked)//OBJ. Subobject_name as Subobj_name,//Sub-object name (already locked)//obj. object_id as obj_id,//Object ID//OBJ. Object_type as Obj_type,//object type//Lock_obj. session_id as session_id,//session session_id//Lock_obj. Oracle_username as Ora_username,//Oracle System User name//lock_obj. Os_user_name as Os_username,//operating system User name//lock_obj. Process as process//Progress number//FROM//(SELECT * from All_objects where object_id in (select object_id from v$locked _object)) obj,//V$locked_object lock_obj//where obj.object_id=lock_obj.object_id; SELECT-SQL2////Features: Check for locked objects and locked session information////if you need to unlock manually, make a note of the session_id,serial#////item against the object you want to unlock, and then run the following ALTER-SQL1// Select Lock_info. owner| | '. ' | | Lock_info. Obj_name as Obj_name,//object name (already locked)//Lock_info. Subobj_name as Subobj_name,//Sub-objectName (already locked)//Sess_info. Machine as machines,//Machinery name//Lock_info. session_id as session_id,//session session_id//Sess_info. serial# as serial#,//session serial#//Lock_info. Ora_username as Ora_username,//Oracle System User name//lock_info. Os_username as Os_username,//operating system User name//lock_info. Process as process,//Progress number//Lock_info. obj_id as obj_id,//Object ID//Lock_info. Obj_type as Obj_type,//object type//Sess_info. Logon_time as Logon_time,//Login time//Sess_info. Programs as program,//name//Sess_info. Status as status,//session state//Sess_info. Lockwait as lockwait,//Waiting for lock//Sess_info. Action as action,//action//Sess_info. Client_info as Client_info//Customer information////from//(//select obj. Owner as owner,//obj. object_name as Obj_name,//OBJ. Subobject_name as Subobj_name,//obj. object_id as obj_id,//OBJ. Object_type as Obj_type,//Lock_obj. session_id as SessION_ID,//Lock_obj. Oracle_username as Ora_username,//Lock_obj. Os_user_name as Os_username,//Lock_obj. Process as process//From//(SELECT * from All_objects where object_id in (select object_id from V$locked_object)) O     BJ,//V$locked_object lock_obj//Where obj.object_id=lock_obj.object_id//) Lock_info,//(///select SID,// serial#,//lockwait,//STATUS,//program,//ACTION,//Client_info,//Logon_time,//MACH INE//from V$session//) Sess_info//where lock_info. Session_id=sess_info. SID; You can kill it if you see it clearly. ALTER-SQL1//function: Kill the session (session_id,serial#), can be manually unlocked//Please manually modify the session_id,serial# for the corresponding Value//NOTE: This function is used with caution, there is a certain destructive, The SQL can disconnect the client and the server//alter SYSTEM KILL SESSION ' session_id,serial# ';

View the Oracle deadlock process and end the deadlock