Oracle Database locking and unlocking

Oracle Database lock and unlock view lock items: Table lock, row lock, etc., the following code

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 is NOT Null

 

The unlock statement is as follows:

alter system kill session'sid,serial';

 

 

1. check the session information of the locked object and the name of the locked object SELECT l. session_id sid, s. serial #, l. locked_mode, l. oracle_username, l. OS _user_name, s. machine, s. terminal, o. object_name, s. logon_timeFROM v $ locked_object l, all_objects o, v $ session sWHERE l. object_id = o. object_idAND l. session_id = s. sidORDER BY sid, s. serial #; -- 2. locate the sid, serial #, OS _user_name, machine name, terminal, and executed statement of the session to lock the table-more SQL _text and actionSELECT l than the preceding statement. session_id sid, s. serial #, l. locked_mode, l. oracle_username, s. user #, l. OS _user_name, s. machine, s. terminal,. SQL _text,. actionFROM v $ sqlarea a, v $ session s, v $ locked_object lWHERE l. session_id = s. sidAND s. prev_ SQL _addr =. addressORDER BY sid, s. serial #; -- 3. locate the sid, serial #, OS _user_name, machine_name, terminal, lock type, modeSELECT s of the locked table. sid, s. serial #, s. username, s. schemaname, s. osuser, s. process, s. machine, s. terminal, s. logon_time, l. typeFROM v $ session s, v $ lock lWHERE s. sid = l. sidAND s. username is not nullorder by sid;

 

This statement finds the locks generated by all the DML statements in the database. It can also be found that any DML statement actually produces two locks, one is the table lock and the other is the row lock. Lock kill command

alter system kill session 'sid,serial#'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 is NOT NULL

 

The methods for merging lock items and batch export statements are as follows:

Set echo on ------- whether to display the executed command content set feedback off -------- whether to display * rows selectedSET heading off -------- whether to display the field name set verify off --------- whether to display the statement before and after the substitution variable is replaced. Set trimspool on -------- field space set pagesize 1000 -------- page size set linesize 50 -------- as per your needs, generate file size define fil = 'd: \ EXP. TXT 'prompt *** spooling to & filspool onspool & filselect' alter system kill session ', ''' | trim (t2.sid) | ', '| trim (t2.serial #) | ''';' from v $ locked_object t1, v $ session t2 where t1.session _ id = t2.sid order by t2.logon _ time; // This part only queries the SID and serial # values; SPOOL OFF

 

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.

col user_name format a10col owner format a10col object_name format a10col object_type format a10SELECT /*+ 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