admin 发表于 2022-1-11 13:42:13

q_lock_info

查看对象锁信息并生成杀锁语句
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;
/




admin 发表于 2023-12-12 17:25:18

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 gv$LOCK l1,
        gv$SESSION s1,
        gv$LOCK l2,
        gv$SESSION s2,
        gv$locked_object lo,
        dba_objects ob,
        gv$process p1,
        gv$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

admin 发表于 2023-12-8 09:57:37

SQL> select * from dba_blockers;

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

admin 发表于 2023-12-8 09:58:23

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 发表于 2023-12-8 10:02:42

SQL> select SESSION_ID,LOCK_TYPE,MODE_HELD,LOCK_ID1,LOCK_ID2 from dba_locks;

SESSION_ID LOCK_TYPE                      MODE_HELD                              LOCK_ID1                                 LOCK_ID2
---------- -------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
      11 XR                              Null                                       4                                        0
      11 RD                              Null                                       1                                        0
      10 Redo Thread                      Exclusive                              1                                        0
      11 Control File               Row-S (SS)                               0                                        0
      11 RS                              Row-S (SS)                               25                                        1
       246 Media Recovery            Share                                       7                                        0
       246 Media Recovery            Share                                       1                                        0
       246 Media Recovery            Share                                       4                                        0
       246 Media Recovery            Share                                       5                                        0
       246 Media Recovery            Share                                       3                                        0
       246 Media Recovery            Share                                       201                                        0

SESSION_ID LOCK_TYPE                      MODE_HELD                              LOCK_ID1                                 LOCK_ID2
---------- -------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
      18 AE                              Share                                       134                                        0
      27 AE                              Share                                       134                                        0
      39 AE                              Share                                       134                                        0
      56 AE                              Share                                       134                                        0
       272 AE                              Share                                       134                                        0
       278 AE                              Share                                       134                                        0
       284 AE                              Share                                       134                                        0
      39 Transaction                      Exclusive                              589851                                        1369
       278 Transaction                      None                                       589851                                        1369
      12 Temp Segment               Row-X (SX)                               3                                        1
         7 KD                              Exclusive                              0                                        0

SESSION_ID LOCK_TYPE                      MODE_HELD                              LOCK_ID1                                 LOCK_ID2
---------- -------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
       246 PW                              Row-X (SX)                               1                                        0
         7 KT                              Share                                       19630                                        0
      39 DML                              Row-X (SX)                               81096                                        0
       278 DML                              Row-X (SX)                               81096                                        0

admin 发表于 2023-12-9 06:49:53


SELECT
    l.session_id,
    s.username,
    s.sid,
    s.serial#,
    l.lock_type,
    l.mode_held,
    l.mode_requested,
    ROUND((sysdate - l.ctime) * 24 * 60, 2) AS minutes_locked
FROM
    v$lock l
JOIN
    v$session s ON l.sid = s.sid
WHERE
    (sysdate - l.ctime) * 24 * 60 > 60;

admin 发表于 2023-12-11 10:26:27

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

admin 发表于 2023-12-12 13:39:39

SELECT s1.sid AS blocking_sid,
        s1.serial# AS blocking_serial,
        s1.sql_id AS blocking_sql_id,
        s1.STATUS AS blocking_status,
        s1.last_call_et AS blocking_last_call_et,
        s2.sid AS blocked_sid,
        s2.serial# AS blocked_serial,
        s2.sql_id AS blocked_sql_id,
        s2.event AS blocked_event,
        s2.STATUS AS blocked_status,
        s2.last_call_et AS blocked_last_call_et
FROM v$session s1,
        v$session s2
WHERE s1.sid = s2.blocking_session

admin 发表于 2023-12-13 16:20:21

gv$,还需要调整
col BLOCKED_OBJECT FOR a40
col BLOCKER_INFO FOR a50
col BLOCKED_INFO FOR a50
col LOCK_SOLVTION FOR a60

SET lines 220

SELECT s1.inst_id,
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# || ',' || '@' || s1.inst_id ||''' immediate;' AS lock_solvtion
FROM gv$LOCK l1,
      gv$SESSION s1,
      gv$LOCK l2,
      gv$SESSION s2,
      gv$locked_object lo,
      dba_objects ob,
      gv$process p1,
      gv$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
order by 1
/

admin 发表于 2023-12-27 10:19:16

REM srdc_lock_lolders.sql - collect Lock holder information
define SRDCNAME='Lock_Holders'
SET MARKUP HTML ON PREFORMAT ON
set TERMOUT off FEEDBACK off VERIFY off TRIMSPOOL on HEADING off
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
   to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
set TERMOUT on MARKUP html preformat on
REM
spool &&SRDCSPOOLNAME..htm
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp:       '||
   to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine:         '||host_name from v$instance
union all
select '| Version:         '||version from v$instance
union all
select '| DBName:          '||name from v$database
union all
select '| Instance:      '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/

set linesize 132 pagesize 66 heading on
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column usernameformat a10heading "Username"
column terminal heading Term format a7
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
column ctime heading "Held for (s)"
select        nvl(S.USERNAME,'Internal') username,
        nvl(S.TERMINAL,'None') terminal,
        L.SID||','||S.SERIAL# Kill,
        U1.NAME||'.'||substr(T1.NAME,1,20) tab,
        decode(L.LMODE,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive',null) lmode,
        decode(L.REQUEST,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive',null) request,
      l.ctime
from        V$LOCK L,
        V$SESSION S,
        SYS.USER$ U1,
        SYS.OBJ$ T1
where        L.SID = S.SID
and        T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and        U1.USER# = T1.OWNER#
and        S.TYPE != 'BACKGROUND'
order by 1,2,5
/

spool off
set markup html off preformat off
页: [1]
查看完整版本: q_lock_info