일반 User가 Table Lock 조회할 수 있도록 View 제공

--revoke select on sys.gv_$locked_object from tsql;
--revoke select on sys.gv_$session from tsql;
--grant select on sys.gv_$locked_object to tsql with grant option;
--grant select on sys.gv_$session to tsql with grant option;

CREATE OR REPLACE VIEW tsql.v_table_lock
AS
    SELECT lo.inst_id,
           lo.session_id,
           s.serial#,
           lo.oracle_username,
           (SELECT object_name
            FROM   all_objects o
            WHERE  o.object_id = lo.object_id
            AND    ROWNUM = 1) locked_table,
           lo.process,
           DECODE(
               lo.locked_mode,
               0, 'NONE: lock requested but not yet obtained',
               1, 'NULL',
               2, 'ROWS_S (SS): Row Share Lock',
               3, 'ROW_X (SX): Row Exclusive Table Lock',
               4, 'SHARE (S): Share Table Lock',
               5, 'S/ROW-X (SSX): Share Row Exclusive Table Lock',
               6, 'Exclusive (X): Exclusive Table Lock',
               'Unknown(' || lo.locked_mode || ')'
           ) locked_mode,
           s.sql_id,
           s.status,
           s.machine,
           s.program,
           s.module,
           (SELECT o.object_name FROM all_objects o WHERE o.object_id = s.plsql_entry_object_id AND ROWNUM = 1) plsql_name,
           s.last_call_et
    FROM   gv$locked_object lo,
           --(SELECT * FROM gv$locked_object WHERE user IN ('SYSTEM','SYS','TSQL') AND oracle_username <> user UNION ALL
           -- SELECT * FROM gv$locked_object WHERE oracle_username = user) lo,
           gv$session s
    WHERE  lo.session_id = s.sid
    AND    lo.inst_id = s.inst_id;

GRANT SELECT ON tsql.v_table_lock TO PUBLIC;
CREATE PUBLIC SYNONYM v_table_lock FOR tsql.v_table_lock;

select * from v_table_lock;