--View lock table process SQL statements 1:select Sess.sid, sess.serial#, Lo.oracle_username, Lo.os_user_name, Ao.object_name, Lo.locked_ Modefrom v$locked_object Lo, dba_objects ao, v$session sesswhere ao.object_id = lo.object_id and lo.session_id = Sess.sid; --view Lock table Process SQL statement 2:select * from v$session T1, v$locked_object t2 where t1.sid = t2. session_id; --Kill the Lock table process:-If there is a record of lock, record the SID and Serial#, the ID of the record to replace the following 738,1429, you can remove the Lockalter system kill session ' 738,1429 '; --Use this to check (you can see which machine which user locked the record, where command is used to kill the locked record session): Select S.sid,s.machine,o.object_ Name,l.oracle_username,l.locked_mode, ' ALTER SYSTEM KILL SESSION ' | | s.sid| | ', ' | | s.serial#| | '; ' Commandfrom v$locked_object l,v$session s,all_objects o where L.session_id=s.sid and l.object_id=o.object_id; 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 ', ' Distributed xaction ', ' CF ' , ' Control File ', ' is ', ' Instance state ', ' FS ', ' File Set ', ' IR ', ' Instance Recovery ', ' ST ', ' Disk Space Transaction ', ' TS ', ' T EMP Segment ', ' IV ', ' Library Cache invalida-tion ', ' LS ', ' Log Start or Switch ', ' RW ', ' Row Wait ', ' SQ ', ' Sequence number ', ' TE ' , ' Extend table ', ' TT ', ' Temp Table ', ' Unknown ') locktypefrom (SELECT * from V$lock) A, All_objects, V$session, v$processwhe Re a.sid > 6and object_name<> ' obj$ ' and a.id1 = All_objects.object_idand A.sid=v$session.sidand v$process.addr= v$session.paddr; --is also found in the data dictionary by writing SQL. --SELECT-SQL1 function: Check the locked object SELECT obj. owner| | '. ' | | Obj. object_name as Obj_name,--//object name (already locked) OBJ. Subobject_name as Subobj_name,--//child object name (already locked) obj. object_id as obj_id,--//object Idobj.object_type as Obj_type,--//object type Lock_obj. session_id as session_id,--//session session_idlock_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--//number from (SELECT * from All_objects where object_id in (select object_id from V$locked_object)) OB J,v$locked_object lock_objwhere obj.object_id=lock_obj.object_id; --select-sql2 Features: Check for locked objects and locked session information-- If you need to unlock the lock manually, note the session_id,serial# entry against the object you want to unlock, and then run the following Alter-sql1select Lock_info. owner| | '. ' | | Lock_info. Obj_name as Obj_name,--//object name (already locked) lock_info. Subobj_name as Subobj_name,--//Sub-object name (already locked) sess_info. Machine as machines,--//name Lock_info. session_id as session_id,--//session Session_idsess_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,--//objectIdlock_info. Obj_type as Obj_type,--//object type Sess_info. Logon_time as Logon_time,--//logon time sess_info. Programs as program, the name of the--//Sess_info. Status as status,--//session state Sess_info. Lockwait as lockwait,--//waits 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 Processfrom (SELECT * from All_objects where object_id in (select object_id from V$locked_object)) obj,v$locked _object lock_objwhere obj.object_id=lock_obj.object_id) lock_info, (select Sid,serial#,lockwait,status,program, Action,client_info,logon_time,machinefrom v$session) Sess_infowhere lock_info. Session_id=sess_info. SID; --you can kill it if you see it clearly. --ALTER-SQL1 function: Kill 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# ';
Go to view the Oracle deadlock process and end the deadlock
