Locks dml lock time

Fiche express
Type	Script SQL*Plus de diagnostic
Voir aussi	Locks dml ddl · Locks blocking · Lockstat

Locks dml lock time liste les verrous DML de type table (v$lock.type = 'TM') actuellement détenus, avec le mode tenu, le mode demandé (s'il y a conflit) et la durée de détention en minutes. C'est le script à utiliser en priorité quand une transaction bloque les autres depuis un moment et qu'il faut identifier depuis combien de temps.

Script

-- Verrous DML de type table (TM), durée de détention en minutes.
-- Restreint aux objets utilisateur (hors schéma SYS).

SET LINESIZE 256
SET PAGESIZE 50000
SET VERIFY OFF

COLUMN instance_name       FORMAT a9  HEADING 'Instance'
COLUMN locking_oracle_user FORMAT a20 HEADING 'Utilisateur'
COLUMN sid_serial          FORMAT a15 HEADING 'SID / Serial#'
COLUMN mode_held           FORMAT a15 HEADING 'Mode détenu'
COLUMN mode_requested       FORMAT a15 HEADING 'Mode demandé'
COLUMN lock_type            FORMAT a15 HEADING 'Type de verrou'
COLUMN object                FORMAT a42 HEADING 'Objet'
COLUMN program               FORMAT a20 HEADING 'Programme'
COLUMN lock_time_min          FORMAT 999,999 HEADING 'Détenu depuis (min)'

SELECT
    i.instance_name                     instance_name
  , l.sid || ' / ' || s.serial#         sid_serial
  , s.username                          locking_oracle_user
  , DECODE(l.lmode
          , 1, NULL
          , 2, 'Row Share'
          , 3, 'Row Exclusive'
          , 4, 'Share'
          , 5, 'Share Row Excl.'
          , 6, 'Exclusive'
          ,    'None')                  mode_held
  , DECODE(l.request
          , 1, NULL
          , 2, 'Row Share'
          , 3, 'Row Exclusive'
          , 4, 'Share'
          , 5, 'Share Row Excl.'
          , 6, 'Exclusive'
          ,    'None')                  mode_requested
  , DECODE(l.type
          , 'TM', 'DML (table)'
          , 'TX', 'Transaction'
          ,       'Autre')              lock_type
  , o.owner || '.' || o.object_name     object
  , ROUND(l.ctime / 60, 2)              lock_time_min
FROM
    v$instance    i
  , v$session     s
  , v$lock        l
  , dba_objects   o
  , dba_tables    t
WHERE
      l.id1         = o.object_id
  AND s.sid          = l.sid
  AND o.owner         = t.owner
  AND o.object_name   = t.table_name
  AND o.owner         <> 'SYS'
  AND l.type          = 'TM'
ORDER BY
    i.instance_name, l.sid
/

Les modes de verrouillage (2 à 6) correspondent à l'échelle standard des TM enqueues Oracle : plus le niveau est élevé, plus le verrou est restrictif. Un mode 3 (Row Exclusive) est normal pendant un DML classique ; un mode 6 (Exclusive), souvent posé par un DDL ou un LOCK TABLE explicite, bloque tout accès concurrent à la table.

Voir aussi

• Locks dml ddl — vue combinée DML + DDL sans le détail des modes
• Locks blocking — sessions bloquantes/bloquées
• Lockstat — statistiques de contention au niveau instance