[DB] Oracle monitoring lock table (Lock table and unlock table)

Oracle monitoring lock table (Lock table and unlock table) 1. query locked tables
Select S. username, <br/> decode (L. type, 'Tm ', 'table lock', 'tx', 'row lock', null) lock_level, <br/> O. owner, <br/> O. object_name, <br/> O. object_type, <br/> S. sid, <br/> S. serial #, <br/> S. terminal, <br/> S. machine, <br/> S. program, <br/> S. osuser <br/> from V $ session S, V $ lock l, dba_objects o <br/> where L. SID = S. sid <br/> and L. id1 = O. object_id (+) <br/> and S. username is not null2. untable -- Kill session Statement <br/> -- '1997 7' format: 'sid, serial # '<br/> alter system kill session '192 2 ';

3. related tables

-- Select * from V $ lock; <br/> select * from V $ sqlarea; <br/> select * from V $ session; <br/> select * from V $ process; <br/> select * from V $ locked_object; <br/> select * From all_objects; <br/> select * from V $ session_wait; <br/> -- 1. check the session information of the locked object and the name of the locked object. <br/> select L. session_id Sid, <br/> S. serial #, <br/> L. locked_mode, <br/> L. oracle_username, <br/> L. OS _user_name, <br/> S. mac Hine, <br/> S. terminal, <br/> O. object_name, <br/> S. logon_time <br/> from V $ locked_object L, all_objects o, V $ session S <br/> where L. object_id = O. object_id <br/> and L. session_id = S. sid <br/> order by SID, S. serial #; <br/> -- 2. locate the SID, serial #, OS _user_name, machine name, terminal, and executed statement of the session to lock the table. <br/> -- SQL _text and action are more than the preceding statement. <br/> select L. session_id Sid, <br/> S. serial #, <br/> L. locked_mode, <br /> L. oracle_username, <br/> S. user #, <br/> L. OS _user_name, <br/> S. machine, <br/> S. terminal, <br/>. SQL _text, <br/>. action <br/> from V $ sqlarea A, V $ session S, V $ locked_object L <br/> where L. session_id = S. sid <br/> and S. prev_ SQL _addr =. address <br/> order by SID, S. serial #; <br/> -- 3. locate the SID, serial #, OS _user_name, machine_name, terminal, type, mode of the lock table. <br/> select S. sid, <br/> S. serial #, <br /> S. username, <br/> S. schemaname, <br/> S. osuser, <br/> S. process, <br/> S. machine, <br/> S. terminal, <br/> S. logon_time, <br/> L. type <br/> from V $ session S, V $ lock l <br/> where S. SID = L. sid <br/> and S. username is not null <br/> order by SID; <br/> -- if a lock wait occurs, we may want to know who has locked the table and who is waiting <br/> -- the following statement can be used to query who has locked the table and who is waiting. <Br/> -- the preceding query result is in a tree structure. If a subnode exists, a waiting occurs. <Br/> -- if you want to know which rollback segment the lock uses, you can also associate it with V $ rollname, xidusn is the USN of the rollback segment. <br/> select lpad ('', decode (L. xidusn, 0, 3, 0) | L. oracle_username user_name, <br/> O. owner, <br/> O. object_name, <br/> O. object_type, <br/> S. sid, <br/> S. serial # <br/> from V $ locked_object L, dba_objects o, V $ session S <br/> where L. object_id = O. object_id <br/> and L. session_id = S. sid <br/> order by O. object_id, xidusn DESC