The DBA_DDL_LOCKS view in Oracle

The DBA_DDL_LOCKS view in Oracle provides information about all the data definition language (DDL) locks in the database. This view is useful for monitoring and diagnosing locking issues related to DDL operations such as CREATE, ALTER, and DROP statements.

Description of DBA_DDL_LOCKS View

The DBA_DDL_LOCKS view contains information about DDL locks that are currently held in the database.

Columns in DBA_DDL_LOCKS View

Some of the important columns in the DBA_DDL_LOCKS view include:

• SESSION_ID: The ID of the session holding the lock.
• OWNER: The owner of the object being locked.
• NAME: The name of the object being locked.
• TYPE: The type of the object being locked (e.g., TABLE, INDEX).
• MODE_HELD: The mode in which the lock is held (e.g., NULL, SHARE, EXCLUSIVE).
• MODE_REQUESTED: The mode in which the lock is requested (if any).

Example Usage

Here’s an example query to retrieve information from the DBA_DDL_LOCKS view:

SELECT SESSION_ID, OWNER, NAME, TYPE, MODE_HELD, MODE_REQUESTED FROM DBA_DDL_LOCKS ORDER BY SESSION_ID;

Detailed Example

Suppose you want to find out which sessions are holding DDL locks on a specific table, and the mode of these locks:

SELECT SESSION_ID, OWNER, NAME, TYPE, MODE_HELD, MODE_REQUESTED FROM DBA_DDL_LOCKS WHERE NAME = 'MY_TABLE' AND OWNER = 'MY_SCHEMA';

Checking for Blocking DDL Locks

To identify sessions that are blocking others with DDL locks, you can combine DBA_DDL_LOCKS with other views such as V$SESSION:

SELECT s1.SID AS blocking_session, s1.SERIAL# AS blocking_serial, s2.SID AS waiting_session, s2.SERIAL# AS waiting_serial, dl.OWNER, dl.NAME, dl.MODE_HELD, dl.MODE_REQUESTED FROM DBA_DDL_LOCKS dl JOIN V$SESSION s1 ON dl.SESSION_ID = s1.SID JOIN V$SESSION s2 ON s1.SID = s2.BLOCKING_SESSION WHERE s2.EVENT = 'enq: TM - DDL lock';

Example: Killing a Session Holding a DDL Lock

In cases where a DDL lock is causing issues, you might need to kill the session holding the lock. Here’s how to identify and kill such a session:

1. Identify the Session:

SELECT 

    SESSION_ID, 

    OWNER, 

    NAME, 

    TYPE, 

    MODE_HELD, 

    MODE_REQUESTED

FROM 

    DBA_DDL_LOCKS

WHERE 

    NAME = 'MY_TABLE'

AND 

    OWNER = 'MY_SCHEMA';

2. Kill the Session:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Conclusion

The DBA_DDL_LOCKS view is an essential tool for database administrators to monitor and diagnose issues related to DDL operations in the Oracle database. It provides detailed information about DDL locks held by sessions, which is crucial for resolving locking and contention issues.

By using DBA_DDL_LOCKS, along with other dynamic performance views such as V$SESSION, administrators can effectively manage and troubleshoot locking problems, ensuring smooth database operations.