TA的每日心情 | 开心 2023-8-9 11:05 |
---|
|
查看对象锁信息并生成杀锁语句
- col BLOCKED_OBJECT FOR a30
- col BLOCKER_INFO FOR a50
- col BLOCKED_INFO FOR a50
- col LOCK_SOLVTION FOR a50
- SET lines 200
- SELECT ob.object_type || ':' || ob.OWNER || '.' || ob.object_name AS blocked_object,
- s1.username || ':' || s1.sid || ',' || s1.serial# || ':' || p1.spid || ':' || s1.STATUS || ':' || s1.sql_id AS blocker_info,
- s2.username || ':' || s2.sid || ',' || s2.serial# || ':' || p2.spid || ':' || s2.STATUS || ':' || s2.sql_id AS blocked_info,
- 'ALTER SYSTEM KILL SESSION ''' || s1.sid || ',' || s1.serial# ||''' immediate;' AS lock_solvtion
- FROM v$LOCK l1,
- v$SESSION s1,
- v$LOCK l2,
- v$SESSION s2,
- v$locked_object lo,
- dba_objects ob,
- v$process p1,
- v$process p2
- WHERE s1.sid = l1.sid
- AND s2.sid = l2.sid
- AND l1.BLOCK = 1
- AND l2.request > 0
- AND l1.id1 = l2.id1
- AND l2.id2 = l2.id2
- AND l1.sid = lo.session_id
- AND lo.object_id = ob.object_id
- AND s1.paddr = p1.addr
- AND s2.paddr = p2.addr
- /
复制代码 查看对象级别的锁信息
- set linesize 1000 pages 100 echo on feedback off
- column LOCK_TYPE format A30
- column LOCK_HELD format A18
- column LOCK_REQUESTED format A11
- column STATUS format A13
- column LOCK_HELD_SEC format 99999999
- column "DB_SID | OS_PID" format A15
- column "LOCKED_OBJECT" format A39
- column "LOCK HOLDER: DB_USER | OS_USER" format A30
- select /*+RULE*/ OWNER||'.'||OBJECT_NAME "LOCKED_OBJECT", ORACLE_USERNAME||' | '||lo.OS_USER_NAME "LOCK HOLDER: DB_USER | OS_USER",l.sid||' | '|| lo.PROCESS "DB_SID,DB_SERIAL# | OS_PID",
- 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)||' | '||
- decode(LMODE,
- 0, 'None',
- 1, 'Null',
- 2, 'row share lock',
- 3, 'row exclusive lock',
- 4, 'Share',
- 5, '(SSX)exclusive lock',
- 6, 'Exclusive', lmode) lock_type,
- l.CTIME LOCK_HELD_SEC,
- decode(REQUEST,
- 0, 'None',
- 1, 'Null',
- 2, 'row share lock',
- 3, 'row exclusive lock',
- 4, 'Share',
- 5, '(SSX)exclusive lock',
- 6, 'Exclusive', request) lock_requested,
- decode(BLOCK,
- 0, 'Not Blocking',
- 1, 'Blocking',
- 2, 'Global', block) status
- from v$locked_object lo, dba_objects do, v$lock l
- where lo.OBJECT_ID = do.OBJECT_ID
- AND l.SID = lo.SESSION_ID
- AND l.BLOCK='1'
- order by OWNER,OBJECT_NAME;
复制代码
- prompt
- prompt Lock Holder Informations
- prompt ===================================
- col event format a35
- col object_name format a25
- select /*+ rule */
- d.sid
- ,decode(d.sql_id,null,d.prev_sql_id,d.sql_id) sql_id
- ,d.event
- ,owner||'.'||object_name object_name
- ,object_type
- from dba_objects a,
- v$locked_object b,
- dba_blockers c,
- v$session d
- where a.object_id=b.object_id
- and b.session_id=c.holding_session
- and c.holding_session=d.sid
- and d.sid not in (select distinct waiting_session from dba_waiters)
- ;
复制代码 杀掉会话
- SELECT 'alter system kill session ''' || a.sid || ',' || a.serial# || ''' immediate;'
- FROM v\$session a,
- v\$locked_object b,
- dba_objects c,
- v\$lock d
- WHERE a.sid = b.session_id
- AND b.session_id = d.sid
- AND b.object_id = c.object_id
- AND c.object_name IN ('T', 'T1')
- AND d.block = 1
- AND d.ctime > 300;
复制代码 plsql杀session
- DECLARE
- CURSOR c_sess IS
- SELECT a.sid, a.serial#
- FROM v$session a,
- v$locked_object b,
- dba_objects c,
- v$lock d
- WHERE a.sid = b.session_id
- AND b.session_id = d.sid
- AND b.object_id = c.object_id
- AND c.object_name IN ('T', 'T1')
- AND d.block = 1
- AND d.ctime > 300;
- v_sid v$session.sid%TYPE;
- v_serial v$session.serial#%TYPE;
- sql_stmt VARCHAR2(1000);
- BEGIN
- OPEN c_sess;
- LOOP
- FETCH c_sess INTO v_sid, v_serial;
- EXIT WHEN c_sess%NOTFOUND;
- sql_stmt := 'alter system kill session ''' || v_sid || ',' || v_serial || ''' immediate';
- EXECUTE IMMEDIATE sql_stmt;
- DBMS_OUTPUT.PUT_LINE(sql_stmt);
- DBMS_OUTPUT.PUT_LINE('session:' || v_sid || ',' || v_serial || ' killed');
- DBMS_OUTPUT.NEW_LINE;
- END LOOP;
- CLOSE c_sess;
- END;
- /
复制代码
|
|