Database lock and session

zhaozj2021-02-16  89

[Q] How to kill a specific database session [A] ALTER SYSTEM KILL Session 'SID, Serial #'; or Alter System Disconnect Session 'SID, Serial #' Immediate; on WIN, ORAKILL provided by Oracle can also kill A thread (actually a Oracle process) on Linux / UNIX, you can directly use Kill to kill the OS process corresponding to the database process.

[Q] How to quickly find the lock and lock Wait [A] The lock of the database is a comparative resource, especially when there is a lock waiting, we must find a waiting lock, there is a possible voice, kill the process. This statement will find the lock generated by all DML statements in the database, and you can find that any DML statement actually produces two locks, one is a table lock, one is a line lock. You can kill the session through Alter System Kill Session 'SID, Serial #' to kill seres 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.OSUser from V $ Session S, V $ LOCK l, dba_objects o where l.sid = s.SID and L.ID1 = Object_ID ( ) and S.Username is not null If a lock is waiting, who can I don't know who is locked? Waiting for the following statement to query who is locked, and who is waiting. SELECT / * Rule * / LPAD ('', Decode (L.XIDUSN, 0, 3, 0)) || L. Oracle_Username User_Name, O.Owner, O.Object_name, Object_Type, S.SID, S. Serial # from v $ locked_Object L, DBA_Objects O, V $ session s where l.object_id = O.Object_id and l.session_id = s.SID ORDER BY O.OBJECT_ID, Xidusn DESC above the query result is a tree structure, if there is The child node is that there is a waiting to occur. If you want to know which roll band is used, you can also associate with V $ rollname, where Xidusn is the USN of the rollback segment.

转载请注明原文地址:https://www.9cbs.com/read-11784.html

New Post(0)