Shulou(Shulou.com)05/31 Report--

This article shows you what lock handling methods are available in Oracle, which are concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. Query the lock situation

Select sid,serial#,event,BLOCKING_SESSION from v$session where event like'% TX%'

2. Query specific information according to SID (negligible)

Select sid,serial#,username,machine,blocking_session from v$session where sid=

3. Kill the conversation

# locate the session based on the SID and SERIAL# found in 1 and 2, and kill

ALTER SYSTEM DISCONNECT SESSION', 'IMMEDIATE

Or

ALTER SYSTEM KILL SESSION','

Attachment:

# query blocking script

Col waiting_session for a20col lock_type for a15col mode_requested for a10col mode_held for a10col lock_id1 for a10col lock_id2 for a10set linesize 120set pagesize 999with dba_locks_cust as (SELECT inst_id | |'_'| | sid session_id, DECODE (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',' Temp Segment' 'IV',' Library Cache Invalidation', 'LS',' Log Start or Switch', 'RW',' Row Wait', 'SQ',' Sequence Number', 'TE',' Extend Table', 'TT',' Temp Table', TYPE) lock_type, DECODE (lmode, 0, 'None' / * Mon Lock equivalent * / 1, 'Null', / * N * / 2,' Row-S (SS)', / * L * / 3, 'Row-X (SX)', / * R * / 4, 'Share' / * S * / 5, 'S/Row-X (SSX)', / * C * / 6, 'Exclusive', / * X * / TO_CHAR (lmode)) mode_held, DECODE (request, 0,' None', / * Mon Lock equivalent * / 1 'Null', / * N * / 2,' Row-S (SS)', / * L * / 3, 'Row-X (SX)', / * R * / 4, 'Share', / * S * / 5,' S/Row-X (SSX)' / * C * / 6, 'Exclusive', / * X * / TO_CHAR (request)) mode_requested, TO_CHAR (id1) lock_id1, TO_CHAR (id2) lock_id2, ctime last_convert, DECODE (block, 0,' Not Blocking', / * Not blocking any other processes * / 1 'Blocking', / * This lock blocks other processes * / 2,' Global', / * This lock is global, so we can't tell * / TO_CHAR (block) blocking_others FROM gv$lock), lock_temp as (select * from dba_locks_cust), lock_holder as (select w.session_id waiting_session, h.session_id holding_session, w.lock_type, h.mode_held W.mode_requested, w.lock_id1, w.lock_id2 from lock_temp w, lock_temp h where h.blocking_others in ('Blocking','Global') and h.mode_held! =' None' and h.mode_held! = 'Null' and w.mode_requested! =' None' and w.lock_type = h.lock_type and w.lock_id1 = h.lock_id1 and w.lock_id2 = h.lock_id2) Lock_holders as (select waiting_session,holding_session,lock_type,mode_held,mode_requested,lock_id1,lock_id2 from lock_holder union all select holding_session, null, 'None', null, null from lock_holder minus select waiting_session, null,' None', null, null from lock_holder) select lpad (', 3* (level-1)) | | waiting_session waiting_session, lock_type,mode_ requested, mode_held Lock_id1, lock_id2 from lock_holdersconnect by prior waiting_session = holding_session start with holding_session is null The above is what lock handling methods are available in Oracle. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.