Script: locked_objects.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Lists all locked objects.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @locked_objects
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    COLUMN owner FORMAT A20
    COLUMN username FORMAT A20
    COLUMN object_owner FORMAT A20
    COLUMN object_name FORMAT A30
    COLUMN locked_mode FORMAT A15

    SELECT lo.session_id AS sid,
           s.serial#,
           NVL(lo.oracle_username, '(oracle)') AS username,
           o.owner AS object_owner,
           o.object_name,
           Decode(lo.locked_mode, 0, 'None',
                                 1, 'Null (NULL)',
                                 2, 'Row-S (SS)',
                                 3, 'Row-X (SX)',
                                 4, 'Share (S)',
                                 5, 'S/Row-X (SSX)',
                                 6, 'Exclusive (X)',
                                 lo.locked_mode) locked_mode,
           lo.os_user_name
    FROM   v$locked_object lo
           JOIN dba_objects o ON o.object_id = lo.object_id
           JOIN v$session s ON lo.session_id = s.sid
    ORDER BY 1, 2, 3, 4;

    SET PAGESIZE 14
    SET VERIFY ON