Locks dml ddl

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

Locks dml ddl liste l'ensemble des verrous DML et DDL actuellement posés sur l'instance, en croisant la session qui détient le verrou, l'objet concerné et le temps d'attente associé. Contrairement à Locks dml lock time, qui se concentre sur la durée de détention des verrous DML de type table (TM), ce script couvre les deux familles de verrous (DBA_LOCKS.LOCK_TYPE IN ('DML', 'DDL')) et donne une vue d'ensemble rapide en cas de blocage suspecté.

Le script ne couvre que l'instance locale : sur une base RAC, il faut l'exécuter sur chaque instance (ou l'adapter avec les vues gv$) pour avoir une vue complète.

Script

-- Verrous DML et DDL actifs sur l'instance courante
-- Colonnes : session, statut, utilisateur, type/mode de verrou, objet, programme client,
-- temps d'attente (minutes).

SET LINESIZE 256
SET PAGESIZE 50000
SET VERIFY OFF

COLUMN instance_name       FORMAT a9  HEADING 'Instance'
COLUMN sid_serial          FORMAT a15 HEADING 'SID / Serial#'
COLUMN session_status      FORMAT a9  HEADING 'Statut'
COLUMN locking_oracle_user FORMAT a20 HEADING 'Utilisateur'
COLUMN lock_type           FORMAT a9  HEADING 'Type de verrou'
COLUMN mode_held           FORMAT a10 HEADING 'Mode'
COLUMN object               FORMAT a42 HEADING 'Objet'
COLUMN program              FORMAT a20 HEADING 'Programme'
COLUMN wait_time_min         FORMAT 999,999 HEADING 'Attente (min)'

SELECT
    i.instance_name                     instance_name
  , l.session_id || ' / ' || s.serial#  sid_serial
  , s.status                            session_status
  , s.username                          locking_oracle_user
  , l.lock_type                         lock_type
  , l.mode_held                         mode_held
  , o.owner || '.' || o.object_name     object
  , SUBSTR(s.program, 1, 20)            program
  , ROUND(s.seconds_in_wait / 60, 2)    wait_time_min
FROM
    v$instance   i
  , v$session    s
  , dba_locks    l
  , dba_objects  o
WHERE
      s.sid = l.session_id
  AND l.lock_type IN ('DML', 'DDL')
  AND l.lock_id1 = o.object_id
ORDER BY
    i.instance_name, l.session_id
/

Le temps d'attente est lu directement dans v$session.seconds_in_wait : depuis Oracle 10g, cette information (auparavant réservée à v$session_wait) est fusionnée dans v$session, ce qui évite une jointure supplémentaire et le risque de doublons qu'elle pouvait introduire.

Voir aussi

• Locks dml lock time — même famille de verrous, focalisée sur la durée de détention
• Locks blocking — identification des sessions bloquantes/bloquées
• Lockstat — statistiques de contention au niveau instance