Sperren / Locks

Sie möchten herausfinden, welche Sperren aktuell in Ihrer Oracle-Datenbank gehalten werden? Das folgende Skript gibt hierüber Auskunft:

SQL
SELECT o.name object_name, u.name owner, lid.*
FROM (SELECT
        s.inst_id, s.SID, s.serial#, p.spid,NVL (s.sql_id, 0),
        s.sql_hash_value,
        DECODE (l.TYPE,
            'TM', l.id1,
            'TX', DECODE (l.request,
                   0, NVL (lo.object_id, -1),
                   s.row_wait_obj#
                  ),
            -1
           ) AS object_id,
         l.TYPE lock_type,
        DECODE (l.lmode,
            0, 'NONE',
            1, 'NULL',
            2, 'ROW SHARE',
            3, 'ROW EXCLUSIVE',
            4, 'SHARE',
            5, 'SHARE ROW EXCLUSIVE',
            6, 'EXCLUSIVE',
            '?'
           ) mode_held,
        DECODE (l.request,
            0, 'NONE',
            1, 'NULL',
            2, 'ROW SHARE',
            3, 'ROW EXCLUSIVE',
            4, 'SHARE',
            5, 'SHARE ROW EXCLUSIVE',
            6, 'EXCLUSIVE',
            '?'
           ) mode_requested,
        l.id1, l.id2, l.ctime time_in_mode,s.row_wait_obj#, 
        s.row_wait_block#, s.row_wait_row#, s.row_wait_file#
     FROM gv$lock l,
        gv$session s,
        gv$process p,
        (SELECT object_id, session_id, xidsqn
         FROM gv$locked_object
         WHERE xidsqn > 0) lo
     WHERE l.inst_id = s.inst_id
      AND s.inst_id = p.inst_id
      AND s.SID = l.SID
      AND p.addr = s.paddr
      AND l.SID = lo.session_id(+)
      AND l.id2 = lo.xidsqn(+)) lid,
    SYS.obj$ o,
    SYS.user$ u
WHERE o.obj#(+) = lid.object_id
AND o.owner# = u.user#(+)
AND object_id <> -1

 

Die Views folgenden Views geben zudem detailliert Auskunft:

  • dba_locks: Sperren-Übersicht
  • dba_dml_locks: Sperren durch DML-Statements (insert, update, delete)
  • dba_ddl_locks: Sperren durch DDL-Statements (create, alter, drop etc.)
  • dba_waiters: Wartende Sessions, die durch Locks blockiert werden
  • dba_blockers: Blockierende Sessions
Oracle-Datenbanken: Sperren
SET LINES 120
COL lock_type       FORMAT A15
COL mode_held       FORMAT A10
COL lock_id1        FORMAT A10
COL lock_id2        FORMAT A10
COL mode_requested  FORMAT A15
COL blocking_others FORMAT A20
 
SELECT session_id, lock_type, mode_held, mode_requested, 
       lock_id1, lock_id2, last_convert, blocking_others
FROM dba_locks;

 

Die Ausgabe kann beispielsweise wie folgt aussehen: 

SESSION_ID LOCK_TYPE       MODE_HELD  MODE_REQUESTED  LOCK_ID1   LOCK_ID2   LAST_CONVERT BLOCKING_OTHERS
---------- --------------- ---------- --------------- ---------- ---------- ------------ -----------------
      2188 XR              Null       None            4          0                     0 Global
      2183 XR              Null       None            0          0              25364304 Global
      2192 Media Recovery  Share      None            52         0              13716927 Global
      2188 RS              Row-S (SS) None            25         1              25364299 Global
      2188 Control File    Row-S (SS) None            0          0              25364304 Global
      2192 DM              Share      None            1          0              25364289 Global
      2189 Redo Thread     Exclusive  None            1          0              25364299 Global

 

Die View dba_dml_locks zeigt nur Sperren, die auf DML-Statements beruhen:

Oracle-Datenbanken: Sperren durch DML Statements
SET LINES 120
COL owner           FORMAT A15
COL name            FORMAT A20
COL mode_held       FORMAT A15
COL mode_requested  FORMAT A15
COL blocking_others FORMAT A20
 
SELECT session_id, owner, name,
        mode_held, mode_requested, last_convert, blocking_others
FROM   dba_dml_locks;

 

Die Ausgabe kann beispielsweise wie folgt aussehen: 

 
SESSION_ID OWNER           NAME                 MODEHELD       MODEREQUESTED  LASTCONVERT BLOCKINGOTHERS
---------- --------------- -------------------- --------------- --------------- ------------ ----------------
      1849 NATDATA        JOB                   Row-X (SX)      None                       0 Global
      1849 NATDATA        NATSTATUS             Row-S (SS)      None                       0 Global
      1849 NATDATA        NATSTATUS             Row-X (SX)      None                       0 Global
      1849 NATDATA        NATSTATUS             Row-X (SX)      None                       0 Global
      1849 NATDATA        NODE                  Row-S (SS)      None                       0 Global
      1849 NATDATA        PROCESSINSTANCE       Row-S (SS)      None                       0 Global
      1849 NATDATA        TOKEN                 Row-X (SX)      None                       0 Global
      1849 NATDATA        NATBEV                Row-X (SX)      None                       0 Global
      1849 NATDATA        ACTION                Row-S (SS)      None                       0 Global
      1849 NATDATA        TRANSITION            Row-S (SS)      None                       0 Global
      1849 NATDATA        BYTEARRAY             Row-S (SS)      None                       0 Global
      1849 NATDATA        LOG                   Row-X (SX)      None                       0 Global
      1849 NATDATA        SWIMLANE              Row-S (SS)      None                       0 Global
      1849 NATDATA        VARIABLE_I            Row-S (SS)      None                       0 Global

Über die View dba_ddl_locks können Sie Sperren ermitteln, die auf DDL-Statements beruhen:

 

Oracle-Datenbanken: DDL-Sperren
SET lines 120
COL owner           FORMAT A20
COL name            FORMAT A30
COL mode_held       FORMAT A15
COL mode_requested  FORMAT A15
 
SELECT session_id, owner, name, type, mode_held, mode_requested
FROM   dba_ddl_locks;

 

Die Ausgabe kann beispielsweise wie folgt aussehen: 

SESSION_ID OWNER                NAME                           TYPE                           MODE_HELD MODE_REQU
---------- -------------------- ------------------------------ ------------------------------ --------- ---------
       162 SYS                  SCHEDULER$_INSTANCE_S          Table/Procedure/Type           Null   None
       161 SYS                  AQ$_ALERT_QT_E                 10                             Null   None
       161 SYS                  DBMS_HA_ALERTS_PRVT            Body                           Null   None
       161 SYS                  DBMS_RCVMAN                    Table/Procedure/Type           Null   None
       161 SYS                  DBMS_PRVT_TRACE                Table/Procedure/Type           Null   None
       161 SYS                  PLITBLM                        Table/Procedure/Type           Null   None
       161 SYS                  PLITBLM                        Table/Procedure/Type           Null   None
       161 SYS                  STANDARD                       Table/Procedure/Type           Null   None
       161 SYS                  DBMS_HA_ALERTS_PRVT            Table/Procedure/Type           Null   None
       161 SYS                  DBMS_PRVT_TRACE                Body                           Null   None
       161 SYS                  DBMS_APPLICATION_INFO          Table/Procedure/Type           Null   None
       161 SYS                  STANDARD                       Body                           Null   None
       161 SYS                  DBMS_APPLICATION_INFO          Body                           Null   None
       161 SYS                  DBMS_BACKUP_RESTORE            Body                           Null   None
       161 SYS                  ALERT_QUE_R                    23                             Null   None
       161 SYS                  ALERT_QUE_R                    23                             Null   None
       161 SYS                  DBMS_BACKUP_RESTORE            Table/Procedure/Type           Null   None
       161 SYS                  DBMS_RCVMAN                    Body                           Null   None
       151 SYS                  DATABASE                       18                             Null   None
       144 SYS                  DATABASE                       18                             Null   None

 

Wartende und blockierende Sessions können Sie über die View dba_waiters  ermitteln:

SQL
SET LINES 120
COL mode_held       FORMAT A15
COL mode_requested  FORMAT A15
 
SELECT waiting_session, holding_session, lock_type, 
       mode_held, mode_requested, lock_id1
FROM dba_waiters;

 

Die Ausgabe kann wie folgt aussehen: 

WAITING_SESSION HOLDING_SESSION LOCK_TYPE                  MODE_HELD       MODE_REQUESTED    LOCK_ID1
--------------- --------------- -------------------------- --------------- --------------- ----------
            144             142 Transaction                Exclusive       Exclusive           655393


Allerdings ist folgende Abfrage sehr viel aufschlußreicher. Sie zeigt den Datenbank-Benutzer, den Rechner (machine) und das Programm der sperrenden und der gesperrten Session:

Oracle-Datenbanken: Blocker und Waiter ermitteln
SET LINES 120
COL waiter            FORMAT A20
COL waiting_machine   FORMAT A20
COL waiting_program   FORMAT A20
COL holder            FORMAT A20
COL holding_machine   FORMAT A20
COL holding_program   FORMAT A20
 
SELECT ws.username waiter, 
       ws.machine waiting_machine, 
       ws.program waiting_program,
       hs.username holder, 
       hs.machine holding_machine, 
       hs.program holding_program,
       dw.lock_type, dw.mode_held, 
       dw.mode_requested
FROM dba_waiters dw, v$session ws, v$session hs
WHERE dw.waiting_session = ws.sid
AND   dw.holding_session = hs.sid;

 

Die Ausgabe kann wie folgt aussehen:

WAITER    WAITING_MACHINE  WAITING_PROGRAM  HOLDER      HOLDING_MACHINE      HOLDING_PROGRAM LOCK_TYPE     MODE_HELD  MODE_REQUESTED
--------- ---------------- ---------------- ----------- -------------------- --------------- ------------- ---------- ---------------
eschmidt  CLIENT_89        app_tma          owiegand    CLIENT_182           sqlplus.exe     Transaction   Exclusive  Exclusive

 

Möchten Sie nur blockierende Benutzer ermitteln, so gibt Ihnen die View dba_blockers Auskunft: 

 
SQL
SET LINES 120
COL holder            FORMAT A20
COL holding_machine   FORMAT A20
COL holding_program   FORMAT A20
 
SELECT hs.username holder, 
       hs.machine holding_machine, 
       hs.program holding_program
FROM dba_blockers dh, v$session hs
WHERE dh.holding_session = hs.sid;

 

Die Ausgabe kann beispielsweise so aussehen: 

HOLDER               HOLDING_MACHINE      HOLDING_PROGRAM
-------------------- -------------------- --------------------
owiegand             client_182           sqlplus.exe