안녕하세요. 먼저 답변주시는 모든분들께 진심으로 감사드립니다.

특정 툴이 없는상태에서 아래처럼 lock을 조회하곤 하는데 맞는지 솔직히 잘 모르겠습니다. 

검증한번 부탁드리고요? ㅎㅎ ... 아울러 아래의 쿼리를 lock holder별로 tree구조로 나타내려는데 저는 잘 모르겠네요...ㅠㅠ

가능할까요? 수고스럽지만 제가 지식이 부족해서 그것도 알려주셨으면 진심..진심..감사드립니다.

SELECT /*+ RULE */

  DECODE(L.BLOCK, 0, '  |-WAITING', 1, 'BLOCKer') BLOCK
  ,(SELECT INSTANCE_NAME FROM GV$INSTANCE WHERE INST_ID = L.INST_ID) INST_ID
  ,S.SID
  ,S.SERIAL#
  ,P.SPID PRO_ID
  ,S.USERNAME ORAUSER
  ,D.OBJECT_NAME OBJECT_NM
  ,S.STATUS
  ,SUBSTR(S.OSUSER,1,15) OSUSER
  ,SUBSTR(S.MACHINE,INSTR(S.MACHINE,'\',-1,1)+1) MACHINE
  ,SUBSTR(S.PROGRAM,1,20) PROGRAM
  ,to_char(S.LOGON_TIME,'YYYYMMDD HH24:MI:SS') LOGON_TIME
  ,S.SQL_ID
  ,S.PREV_SQL_ID
  ,DECODE (S.COMMAND, 0, 'No command',1, 'CREATE TABLE',2, 'INSERT',3, 'SELECT대기-다른세션에lock으로',4, 'CREATE CLUSTER',5, 'ALTER CLUSTER',6, 'UPDATE',7, 'DELETE',8, 'DROP CLUSTER',9, 'CREATE INDEX',10, 'DROP INDEX',
   11, 'ALTER INDEX',12, 'DROP TABLE',13, 'CREATE SEQUENCE',14, 'ALTER SEQUENCE',15, 'ALTER TABLE',16, 'DROP SEQUENCE',17, 'GRANT',18, 'REVOKE',19, 'CREATE SYNONYM',20, 'DROP SYNONYM',
   21, 'CREATE VIEW',22, 'DROP VIEW',23, 'VALIDATE INDEX',24, 'CREATE PROCEDURE',25, 'ALTER PROCEDURE',26, 'LOCK TABLE',27, 'NO OPERATION',28, 'RENAME',29, 'COMMENT',30, 'AUDIT',
   31, 'NOAUDIT',32, 'CREATE DATABASE LINK',33, 'DROP DATABASE LINK',34, 'CREATE DATABASE',35, 'ALTER DATABASE',36, 'CREATE ROLLBACK SEGMENT',37, 'ALTER ROLLBACK SEGMENT',38, 'DROP ROLLBACK SEGMENT',39, 'CREATE TABLESPACE',40, 'ALTER TABLESPACE',
   41, 'DROP TABLESPACE',42, 'ALTER SESSION',43, 'ALTER USER',44, 'COMMIT',45, 'ROLLBACK',46, 'SAVEPOINT',47, 'PL/SQL EXECUTE',48, 'SET TRANSACTION',49, 'ALTER SYSTEM SWITCH LOG',50, 'EXPLAIN',
   51, 'CREATE USER',52, 'CREATE ROLE',53, 'DROP USER',54, 'DROP ROLE',55, 'SET ROLE',56, 'CREATE SCHEMA',57, 'CREATE CONTROL FILE',58, 'ALTER TRACING',59, 'CREATE TRIGGER',60, 'ALTER TRIGGER',
   61, 'DROP TRIGGER',62, 'ANALYZE TABLE',63, 'ANALYZE INDEX',64, 'ANALYZE CLUSTER',65, 'CREATE PROFILE',67, 'DROP PROFILE',68, 'ALTER PROFILE',69, 'DROP PROCEDURE',70, 'ALTER RESOURCE COST',
   71, 'CREATE SNAPSHOT LOG',72, 'ALTER SNAPSHOT LOG',73, 'DROP SNAPSHOT LOG',74, 'CREATE SNAPSHOT',75, 'ALTER SNAPSHOT',76, 'DROP SNAPSHOT',79, 'ALTER ROLE',
   85, 'TRUNCATE TABLE',86, 'TRUNCATE CLUSTER',88, 'ALTER VIEW',
   91, 'CREATE FUNCTION',92, 'ALTER FUNCTION',93, 'DROP FUNCTION',94, 'CREATE PACKAGE', 95, 'ALTER PACKAGE',96, 'DROP PACKAGE',
   97, 'CREATE PACKAGE BODY',98, 'ALTER PACKAGE BODY',99, 'DROP PACKAGE BODY',TO_CHAR (S.COMMAND)) COMMAND
  ,L.ID1
  ,L.ID2
  ,DECODE (L.TYPE, 'MR', 'Media Recovery','RT', 'Redo Thread','UN', 'User Name', 'TX', 'Transaction',
          'TM', 'DML', 'UL', 'PL/SQL User Lock', 'DX', 'Distributed Xaction', 'CF', 'Control File',
          'IS', 'Instance State', 'FS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction',
          'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation', 'LS', 'Log Start or Switch',
          'RW', 'Row Wait', 'SQ', 'Sequence Number', 'TE', 'Extend Table', 'TT', 'Temp Table', 'JQ', 'Job Queue', L.TYPE) LTYPE
  ,DECODE( L.LMODE, 1, 'NO LOCK', 2, 'ROW SHARE(RS(SS))', 3, 'ROW EXCLUSIVE(RX(SX))', 4, 'SHARE(S)', 5, 'SHARE ROW EXCLUSIVE(SRX)', 6, 'EXCLUSIVE(X-DDL)', NULL) LMODE
  ,DECODE( L.REQUEST, 1, 'NO LOCK', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', NULL) REQUEST
  ,SUBSTR(S.EVENT,1,30) EVENT
  ,TO_CHAR(TRUNC(L.CTIME/3600),'FM9900') || ':' ||TO_CHAR(TRUNC(MOD(L.CTIME,3600)/60),'FM00') || ':' ||TO_CHAR(MOD(L.CTIME,60),'FM00') WAIT_TIME_MIN
  ,SUBSTR(Q.SQL_TEXT,1,50) SQLTEXT                    /*-- SQLTEXT가 SELECT로 표시될 경우 :PREV_SQL_ID 값으로 sql을 가져온 것임 */
  ,case when S.BLOCKING_SESSION_STATUS='VALID'       then '이세션은 '||S.BLOCKING_SESSION||'세션에 의해 BLOCKED 됨'
       when S.BLOCKING_SESSION_STATUS='NO HOLDER'   then '이세션을 BLOCKING하는세션은 없다.'
       when S.BLOCKING_SESSION_STATUS='NOT IN WAIT' then '이세션은 대기중이 아님' end BLOCKING_SESS_STATUS
  ,S.CREATOR_SERIAL# PARENT_Serial
FROM GV$PROCESS P, GV$SESSION S, GV$LOCK L, GV$LOCKED_OBJECT O, DBA_OBJECTS D, GV$SQLAREA Q
WHERE P.INST_ID=S.INST_ID
  AND P.ADDR=S.PADDR
  AND S.INST_ID=L.INST_ID
  AND S.SID=L.SID
  AND S.SID=O.SESSION_ID
  AND S.INST_ID=O.INST_ID
  AND D.OBJECT_ID=O.OBJECT_ID
  AND S.INST_ID=Q.INST_ID(+)
  AND (L.ID1,L.ID2,L.TYPE) IN (SELECT L2.ID1,L2.ID2,L2.TYPE FROM gV$LOCK L2 )
  and ( ( CASE WHEN S.SQL_ID IS NULL THEN S.PREV_SQL_ID END ) = Q.SQL_ID
          OR ( CASE WHEN S.SQL_ID IS NOT NULL THEN S.SQL_ID END ) = Q.SQL_ID   )  /*--원래 조건 S.SQL_ID=L.SQL_ID */
ORDER BY L.CTIME desc;