Oracle Database

--
-- Blocking Locks (Verbose)
--
-- Shows blocked session, blocking session and details of the programs and machines they are running from.
-- In addition, shows the CTIME value which details how long the blocking session has had hold of the lock
--
-- Best run from SQL Developer where the output is formatted.  Running from SQLPLUS makes it hard to read
-- due to the verbose nature of the query.
--
 
SELECT
    sysdate,
    c.blocker_inst,
    c.blocker_sid,
    c.blocker_username,
    c.blocker_sql_id,
    c.blocker_program,
    c.blocker_machine,
    c.blocker_info,
    c.blocker_action,
    c.blocker_client,
    c.blockee_inst,
    c.blockee_sid,
    c.blockee_username,
    c.blockee_sql_id,
    c.blockee_program,
    c.blockee_machine,
    c.blockee_action,
    c.blockee_info,
    c.blockee_client,
    d.os_user_name locked_os_user_name,
    d.oracle_username locked_username,
    b.owner object_owner,
    b.object_name,
    b.object_type,
    c.ctime "Time Locked (s)"
FROM
    gv$locked_object d,
    dba_objects b,
    (
    SELECT
        ( SELECT username FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_username,
        ( SELECT sql_id FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_sql_id,
        ( SELECT program FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_program,
        ( SELECT machine FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_machine,
        ( SELECT action FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_action,
        ( SELECT client_info FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_info,
        ( SELECT client_identifier FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_client,
        y.sid blocker_sid,
        y.inst_id blocker_inst,
        ( SELECT username FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_username,
        ( SELECT sql_id FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_sql_id,
        ( SELECT program FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_program,
        ( SELECT machine FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_machine,
        ( SELECT action FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_action,
        ( SELECT client_info FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_info,
        ( SELECT client_identifier FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_client,
        z.sid blockee_sid,
        z.inst_id blockee_inst,
        z.ctime 
    FROM
        gv$lock y,
        gv$lock z
    WHERE
        y.block = 1 AND
        z.request > 0 AND
        y.id1 = z.id1 AND
        y.id2 = z.id2
    ) c
WHERE
     d.OBJECT_ID = b.OBJECT_ID AND
     d.inst_id = c.blockee_inst AND
     d.session_id = c.blockee_sid
/