q_lock_info

admin

查看对象锁信息并生成杀锁语句

1. col BLOCKED_OBJECT FOR a30
   
2. col BLOCKER_INFO FOR a50
   
3. col BLOCKED_INFO FOR a50
   
4. col LOCK_SOLVTION FOR a50
   
5. 
   
6. SET lines 200
   
7. 
   
8. SELECT ob.object_type || ':' || ob.OWNER || '.' || ob.object_name AS blocked_object,
   
9.         s1.username || ':' || s1.sid || ',' || s1.serial# || ':' || p1.spid || ':' || s1.STATUS || ':' || s1.sql_id AS blocker_info,
   
10.                 s2.username || ':' || s2.sid || ',' || s2.serial# || ':' || p2.spid || ':' || s2.STATUS || ':' || s2.sql_id AS blocked_info,
    
11.         'ALTER SYSTEM KILL SESSION ''' || s1.sid || ',' || s1.serial# ||''' immediate;' AS lock_solvtion
    
12. FROM v$LOCK l1,
    
13.         v$SESSION s1,
    
14.         v$LOCK l2,
    
15.         v$SESSION s2,
    
16.         v$locked_object lo,
    
17.         dba_objects ob,
    
18.         v$process p1,
    
19.         v$process p2
    
20. WHERE s1.sid = l1.sid
    
21.         AND s2.sid = l2.sid
    
22.         AND l1.BLOCK = 1
    
23.         AND l2.request > 0
    
24.         AND l1.id1 = l2.id1
    
25.         AND l2.id2 = l2.id2
    
26.         AND l1.sid = lo.session_id
    
27.         AND lo.object_id = ob.object_id
    
28.         AND s1.paddr = p1.addr
    
29.         AND s2.paddr = p2.addr
    
30. /

复制代码

查看对象级别的锁信息

1. set linesize 1000 pages 100 echo on feedback off
   
2. column LOCK_TYPE                        format A30
   
3. column LOCK_HELD                        format A18
   
4. column LOCK_REQUESTED                   format A11
   
5. column STATUS                           format A13
   
6. column LOCK_HELD_SEC                    format 99999999
   
7. column "DB_SID | OS_PID"                format A15
   
8. column "LOCKED_OBJECT"                  format A39
   
9. column "LOCK HOLDER: DB_USER | OS_USER" format A30
   
10. 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",
    
11.         decode(TYPE,
    
12.                 'MR', 'Media Recovery',
    
13.                 'RT', 'Redo Thread',
    
14.                 'UN', 'User Name',
    
15.                 'TX', 'Transaction',
    
16.                 'TM', 'DML',
    
17.                 'UL', 'PL/SQL User Lock',
    
18.                 'DX', 'Distributed Xaction',
    
19.                 'CF', 'Control File',
    
20.                 'IS', 'Instance State',
    
21.                 'FS', 'File Set',
    
22.                 'IR', 'Instance Recovery',
    
23.                 'ST', 'Disk Space Transaction',
    
24.                 'TS', 'Temp Segment',
    
25.                 'IV', 'Library Cache Invalidation',
    
26.                 'LS', 'Log Start or Switch',
    
27.                 'RW', 'Row Wait',
    
28.                 'SQ', 'Sequence Number',
    
29.                 'TE', 'Extend Table',
    
30.                 'TT', 'Temp Table', type)||' | '||
    
31.         decode(LMODE,
    
32.                 0, 'None',
    
33.                 1, 'Null',
    
34.                 2, 'row share lock',
    
35.                 3, 'row exclusive lock',
    
36.                 4, 'Share',
    
37.                 5, '(SSX)exclusive lock',
    
38.                 6, 'Exclusive', lmode) lock_type,
    
39.                 l.CTIME LOCK_HELD_SEC,
    
40.         decode(REQUEST,
    
41.                 0, 'None',
    
42.                 1, 'Null',
    
43.                 2, 'row share lock',
    
44.                 3, 'row exclusive lock',
    
45.                 4, 'Share',
    
46.                 5, '(SSX)exclusive lock',
    
47.                 6, 'Exclusive', request) lock_requested,
    
48.         decode(BLOCK,
    
49.                 0, 'Not Blocking',
    
50.                 1, 'Blocking',
    
51.                 2, 'Global', block) status
    
52. from    v$locked_object lo, dba_objects do, v$lock l
    
53. where   lo.OBJECT_ID = do.OBJECT_ID
    
54. AND     l.SID = lo.SESSION_ID
    
55. AND l.BLOCK='1'
    
56. order by OWNER,OBJECT_NAME;

复制代码

1. prompt
   
2. prompt Lock Holder Informations
   
3. prompt ===================================
   
4. col event format a35
   
5. col object_name format a25
   
6. select /*+ rule */
   
7.        d.sid
   
8.       ,decode(d.sql_id,null,d.prev_sql_id,d.sql_id) sql_id
   
9.       ,d.event
   
10.       ,owner||'.'||object_name object_name
    
11.       ,object_type
    
12. from dba_objects     a,
    
13.      v$locked_object b,
    
14.      dba_blockers    c,
    
15.      v$session       d
    
16. where a.object_id=b.object_id
    
17. and   b.session_id=c.holding_session
    
18. and   c.holding_session=d.sid
    
19. and   d.sid not in (select distinct waiting_session from dba_waiters)
    
20. ;

复制代码

杀掉会话

1. SELECT 'alter system kill session ''' || a.sid || ',' || a.serial# || ''' immediate;'
   
2. FROM v\$session a,
   
3.         v\$locked_object b,
   
4.         dba_objects c,
   
5.         v\$lock d
   
6. WHERE a.sid = b.session_id
   
7.         AND b.session_id = d.sid
   
8.         AND b.object_id = c.object_id
   
9.         AND c.object_name IN ('T', 'T1')
   
10.         AND d.block = 1
    
11.         AND d.ctime > 300;

复制代码

plsql杀session

1. DECLARE
   
2.   CURSOR c_sess IS
   
3.     SELECT a.sid, a.serial#
   
4.     FROM v$session a,
   
5.       v$locked_object b,
   
6.       dba_objects c,
   
7.       v$lock d
   
8.     WHERE a.sid = b.session_id
   
9.       AND b.session_id = d.sid
   
10.       AND b.object_id = c.object_id
    
11.       AND c.object_name IN ('T', 'T1')
    
12.       AND d.block = 1
    
13.       AND d.ctime > 300;
    
14.   v_sid v$session.sid%TYPE;
    
15.   v_serial v$session.serial#%TYPE;
    
16.   sql_stmt VARCHAR2(1000);
    
17. BEGIN
    
18.   OPEN c_sess;
    
19.   LOOP
    
20.     FETCH c_sess INTO v_sid, v_serial;
    
21.     EXIT WHEN c_sess%NOTFOUND;
    
22.     sql_stmt := 'alter system kill session ''' || v_sid || ',' || v_serial || ''' immediate';
    
23.     EXECUTE IMMEDIATE sql_stmt;
    
24.         DBMS_OUTPUT.PUT_LINE(sql_stmt);
    
25.     DBMS_OUTPUT.PUT_LINE('session:' || v_sid || ',' || v_serial || ' killed');
    
26.     DBMS_OUTPUT.NEW_LINE;
    
27.   END LOOP;
    
28.   CLOSE c_sess;
    
29. END;
    
30. /

复制代码

admin

1. col BLOCKED_OBJECT FOR a30
   
2. col BLOCKER_INFO FOR a50
   
3. col BLOCKED_INFO FOR a50
   
4. col LOCK_SOLVTION FOR a50
   
5. 
   
6. SET lines 200
   
7. 
   
8. SELECT ob.object_type || ':' || ob.OWNER || '.' || ob.object_name AS blocked_object,
   
9.         s1.username || ':' || s1.sid || ',' || s1.serial# || ':' || p1.spid || ':' || s1.STATUS || ':' || s1.sql_id AS blocker_info,
   
10.         s2.username || ':' || s2.sid || ',' || s2.serial# || ':' || p2.spid || ':' || s2.STATUS || ':' || s2.sql_id AS blocked_info,
    
11.         'ALTER SYSTEM KILL SESSION ''' || s1.sid || ',' || s1.serial# || ''' immediate;' AS lock_solvtion
    
12. FROM gv$LOCK l1,
    
13.         gv$SESSION s1,
    
14.         gv$LOCK l2,
    
15.         gv$SESSION s2,
    
16.         gv$locked_object lo,
    
17.         dba_objects ob,
    
18.         gv$process p1,
    
19.         gv$process p2
    
20. WHERE s1.sid = l1.sid
    
21.         AND s2.sid = l2.sid
    
22.         AND l1.BLOCK = 1
    
23.         AND l2.request > 0
    
24.         AND l1.id1 = l2.id1
    
25.         AND l2.id2 = l2.id2
    
26.         AND l1.sid = lo.session_id
    
27.         AND lo.object_id = ob.object_id
    
28.         AND s1.paddr = p1.addr
    
29.         AND s2.paddr = p2.addr

复制代码

admin

SQL> select * from dba_blockers;

HOLDING_SESSION     CON_ID
--------------- ----------
             39          0

admin

SQL> select * from dba_waiters;

WAITING_SESSION WAITING_CON_ID HOLDING_SESSION HOLDING_CON_ID LOCK_TYPE                  MODE_HELD                                  MODE_REQUESTED                             LOCK_ID1        LOCK_ID2
--------------- -------------- --------------- -------------- -------------------------- ---------------------------------------- ---------------------------------------- ---------- ----------
            278              0                    39                    0 Transaction                 Exclusive                                  Exclusive                                       589851            1369

admin

1. SQL> select SESSION_ID,LOCK_TYPE,MODE_HELD,LOCK_ID1,LOCK_ID2 from dba_locks;
   
2. 
   
3. SESSION_ID LOCK_TYPE                      MODE_HELD                                LOCK_ID1                                 LOCK_ID2
   
4. ---------- -------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
   
5.         11 XR                              Null                                       4                                        0
   
6.         11 RD                              Null                                       1                                        0
   
7.         10 Redo Thread                      Exclusive                                1                                        0
   
8.         11 Control File               Row-S (SS)                               0                                        0
   
9.         11 RS                              Row-S (SS)                               25                                        1
   
10.        246 Media Recovery              Share                                       7                                        0
    
11.        246 Media Recovery              Share                                       1                                        0
    
12.        246 Media Recovery              Share                                       4                                        0
    
13.        246 Media Recovery              Share                                       5                                        0
    
14.        246 Media Recovery              Share                                       3                                        0
    
15.        246 Media Recovery              Share                                       201                                        0
    
16. 
    
17. SESSION_ID LOCK_TYPE                      MODE_HELD                                LOCK_ID1                                 LOCK_ID2
    
18. ---------- -------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
    
19.         18 AE                              Share                                       134                                        0
    
20.         27 AE                              Share                                       134                                        0
    
21.         39 AE                              Share                                       134                                        0
    
22.         56 AE                              Share                                       134                                        0
    
23.        272 AE                              Share                                       134                                        0
    
24.        278 AE                              Share                                       134                                        0
    
25.        284 AE                              Share                                       134                                        0
    
26.         39 Transaction                      Exclusive                                589851                                        1369
    
27.        278 Transaction                      None                                       589851                                        1369
    
28.         12 Temp Segment               Row-X (SX)                               3                                        1
    
29.          7 KD                              Exclusive                                0                                        0
    
30. 
    
31. SESSION_ID LOCK_TYPE                      MODE_HELD                                LOCK_ID1                                 LOCK_ID2
    
32. ---------- -------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
    
33.        246 PW                              Row-X (SX)                               1                                        0
    
34.          7 KT                              Share                                       19630                                        0
    
35.         39 DML                              Row-X (SX)                               81096                                        0
    
36.        278 DML                              Row-X (SX)                               81096                                        0
    

复制代码

admin

1. 
   
2. SELECT
   
3.     l.session_id,
   
4.     s.username,
   
5.     s.sid,
   
6.     s.serial#,
   
7.     l.lock_type,
   
8.     l.mode_held,
   
9.     l.mode_requested,
   
10.     ROUND((sysdate - l.ctime) * 24 * 60, 2) AS minutes_locked
    
11. FROM
    
12.     v$lock l
    
13. JOIN
    
14.     v$session s ON l.sid = s.sid
    
15. WHERE
    
16.     (sysdate - l.ctime) * 24 * 60 > 60;

复制代码

admin

1. select 'alter system kill session '''||a.sid||','||a.serial#||''' immediate -- '||username||'@'||machine||' ('||program||');'
   
2. from v$session a ,v$locked_object b,dba_objects c
   
3. where a.sid = b.session_id
   
4. and C.OBJECT_ID = b.OBJECT_ID
   
5. and C.OBJECT_NAME in ('T','T1')
   
6. AND A.status='INACTIVE' and a.last_call_et>1800
   
7. and a.OSUSER='oracle'
   
8. and a.type='USER';

复制代码

admin

1. SELECT s1.sid AS blocking_sid,
   
2.         s1.serial# AS blocking_serial,
   
3.         s1.sql_id AS blocking_sql_id,
   
4.         s1.STATUS AS blocking_status,
   
5.         s1.last_call_et AS blocking_last_call_et,
   
6.         s2.sid AS blocked_sid,
   
7.         s2.serial# AS blocked_serial,
   
8.         s2.sql_id AS blocked_sql_id,
   
9.         s2.event AS blocked_event,
   
10.         s2.STATUS AS blocked_status,
    
11.         s2.last_call_et AS blocked_last_call_et
    
12. FROM v$session s1,
    
13.         v$session s2
    
14. WHERE s1.sid = s2.blocking_session

复制代码

admin

gv$,还需要调整

1. col BLOCKED_OBJECT FOR a40
   
2. col BLOCKER_INFO FOR a50
   
3. col BLOCKED_INFO FOR a50
   
4. col LOCK_SOLVTION FOR a60
   
5. 
   
6. SET lines 220
   
7. 
   
8. SELECT s1.inst_id,
   
9. ob.object_type || ':' || ob.OWNER || '.' || ob.object_name AS blocked_object,
   
10.         s1.username || ':' || s1.sid || ',' || s1.serial# || ':' || p1.spid || ':' || s1.STATUS || ':' || s1.sql_id AS blocker_info,
    
11.         s2.username || ':' || s2.sid || ',' || s2.serial# || ':' || p2.spid || ':' || s2.STATUS || ':' || s2.sql_id AS blocked_info,
    
12.         'ALTER SYSTEM KILL SESSION ''' || s1.sid || ',' || s1.serial# || ',' || '@' || s1.inst_id ||''' immediate;' AS lock_solvtion
    
13. FROM gv$LOCK l1,
    
14.         gv$SESSION s1,
    
15.         gv$LOCK l2,
    
16.         gv$SESSION s2,
    
17.         gv$locked_object lo,
    
18.         dba_objects ob,
    
19.         gv$process p1,
    
20.         gv$process p2
    
21. WHERE s1.sid = l1.sid
    
22.         AND s2.sid = l2.sid
    
23.         AND l1.BLOCK = 1
    
24.         AND l2.request > 0
    
25.         AND l1.id1 = l2.id1
    
26.         AND l2.id2 = l2.id2
    
27.         AND l1.sid = lo.session_id
    
28.         AND lo.object_id = ob.object_id
    
29.         AND s1.paddr = p1.addr
    
30.         AND s2.paddr = p2.addr
    
31. order by 1
    
32. /

复制代码

admin

1. REM srdc_lock_lolders.sql - collect Lock holder information
   
2. define SRDCNAME='Lock_Holders'
   
3. SET MARKUP HTML ON PREFORMAT ON
   
4. set TERMOUT off FEEDBACK off VERIFY off TRIMSPOOL on HEADING off
   
5. COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
   
6. select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
   
7.      to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
   
8. set TERMOUT on MARKUP html preformat on
   
9. REM
   
10. spool &&SRDCSPOOLNAME..htm
    
11. select '+----------------------------------------------------+' from dual
    
12. union all
    
13. select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
    
14. union all
    
15. select '| Timestamp:       '||
    
16.      to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
    
17. union all
    
18. select '| Machine:         '||host_name from v$instance
    
19. union all
    
20. select '| Version:         '||version from v$instance
    
21. union all
    
22. select '| DBName:          '||name from v$database
    
23. union all
    
24. select '| Instance:        '||instance_name from v$instance
    
25. union all
    
26. select '+----------------------------------------------------+' from dual
    
27. /
    
28. 
    
29. set linesize 132 pagesize 66 heading on
    
30. break on Kill on username on terminal
    
31. column Kill heading 'Kill String' format a13
    
32. column res heading 'Resource Type' format 999
    
33. column id1 format 9999990
    
34. column id2 format 9999990
    
35. column lmode heading 'Lock Held' format a20
    
36. column request heading 'Lock Requested' format a20
    
37. column serial# format 99999
    
38. column username  format a10  heading "Username"
    
39. column terminal heading Term format a7
    
40. column tab format a35 heading "Table Name"
    
41. column owner format a9
    
42. column Address format a18
    
43. column ctime heading "Held for (s)"
    
44. select        nvl(S.USERNAME,'Internal') username,
    
45.         nvl(S.TERMINAL,'None') terminal,
    
46.         L.SID||','||S.SERIAL# Kill,
    
47.         U1.NAME||'.'||substr(T1.NAME,1,20) tab,
    
48.         decode(L.LMODE,1,'No Lock',
    
49.                 2,'Row Share',
    
50.                 3,'Row Exclusive',
    
51.                 4,'Share',
    
52.                 5,'Share Row Exclusive',
    
53.                 6,'Exclusive',null) lmode,
    
54.         decode(L.REQUEST,1,'No Lock',
    
55.                 2,'Row Share',
    
56.                 3,'Row Exclusive',
    
57.                 4,'Share',
    
58.                 5,'Share Row Exclusive',
    
59.                 6,'Exclusive',null) request,
    
60.         l.ctime
    
61. from        V$LOCK L,  
    
62.         V$SESSION S,
    
63.         SYS.USER$ U1,
    
64.         SYS.OBJ$ T1
    
65. where        L.SID = S.SID  
    
66. and        T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)  
    
67. and        U1.USER# = T1.OWNER#
    
68. and        S.TYPE != 'BACKGROUND'
    
69. order by 1,2,5
    
70. /
    
71. 
    
72. spool off
    
73. set markup html off preformat off

复制代码