「Query」오라클 참고 쿼리

인터넷 상에서 떠도는 쿼리들을 내가 사용하는 방식으로 수정하여 사용 중이다.

-- 테이블 정보, 컬럼 정보, Trigger 정보   
SELECT A.OWNER, A.TABLE_NAME, REPLACE(REPLACE(C.COMMENTS, CHR(10), ' '), CHR(13), ' ') TABLE_COMMENTS
     , F.TRIGGER_NAME ,   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(F.TRIGGERING_EVENT,'UPDATE','U'),'OR','/'),'DELETE','D'),'INSERT','I'),' ','') CRDU
     , F.TRIGGERING_EVENT 
     , A.COLUMN_ID
     , NVL2(D.COLUMN_NAME,'PK','') PK
--    , DECODE(D.CONSTRAINT_TYPE,'P','PK',DECODE(D.CONSTRAINT_TYPE,'F','FK','')) "PK/FK"
     , A.COLUMN_NAME, REPLACE(REPLACE(B.COMMENTS, CHR(10), ' '), CHR(13), ' ') COLUMNS_COMMENTS
     , A.DATA_TYPE
     , A.DATA_LENGTH
     , DECODE(A.NULLABLE,'Y','YES','NO') NULLABLE
--     , A.NULLABLE
     , A.DATA_DEFAULT 
     , A.*, B.*, F.*
FROM ALL_TAB_COLUMNS A, ALL_COL_COMMENTS B, ALL_TAB_COMMENTS C, 
    (SELECT COLS.TABLE_NAME, COLS.COLUMN_NAME, COLS.OWNER, CONS.CONSTRAINT_TYPE 
       FROM ALL_CONSTRAINTS CONS, ALL_CONS_COLUMNS COLS
      WHERE COLS.TABLE_NAME = :TABLE_NAME
        AND CONS.CONSTRAINT_TYPE IN ('P')
--        AND CONS.CONSTRAINT_TYPE IN ('P','F')
        AND CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
        AND CONS.OWNER = COLS.OWNER
        AND CONS.OWNER = :OWNER) D, 
     ALL_TRIGGERS F
WHERE A.OWNER = B.OWNER
  AND A.TABLE_NAME = B.TABLE_NAME 
  AND A.COLUMN_NAME = B.COLUMN_NAME 
  AND A.OWNER = C.OWNER
  AND A.TABLE_NAME = C.TABLE_NAME 
  AND A.TABLE_NAME  = D.TABLE_NAME (+)
  AND A.OWNER = D.OWNER (+) 
  AND A.COLUMN_NAME = D.COLUMN_NAME(+)
  AND A.OWNER  = F.TABLE_OWNER (+)
  AND A.TABLE_NAME = F.TABLE_NAME(+)
  AND A.OWNER = :OWNER 
   AND A.TABLE_NAME = :TABLE_NAME   
 ORDER BY A.COLUMN_ID ;
 
 
-- FUNCTION/PROCEDURE/TRIGGER 정보
SELECT * FROM ALL_SOURCE
 WHERE (TYPE = 'FUNCTION' OR TYPE = 'PROCEDURE' OR TYPE = 'TRIGGER'   ) 
 AND NAME = DECODE(:NAME, NULL, NAME, :NAME)
 AND OWNER = :OWNER
ORDER BY LINE 
   ;
   
 
-- 테이블 정보   
SELECT A.OWNER, A.TABLE_NAME, B.COMMENTS, A.*, B.* FROM ALL_ALL_TABLES A, ALL_TAB_COMMENTS B
 WHERE A.OWNER = B.OWNER 
   AND A.TABLE_NAME = B.TABLE_NAME 
   AND A.OWNER = :OWNER
   AND (A.TABLE_NAME LIKE '%'||:TABLE_NAME||'%' OR B.COMMENTS LIKE '%'||:TABLE_NAME||'%')
 ORDER BY A.TABLE_NAME 
  
-- WHERE 필드명 LIKE 변수명||'%';
  ;
  


-- 컬럼 COMMENTS 조회
SELECT * FROM ALL_COL_COMMENTS
 WHERE OWNER = :OWNER
   AND (COMMENTS LIKE '%'||:COMMENTS||'%' OR COLUMN_NAME LIKE '%'||:COMMENTS||'%')
    ;


-- ORACLE SESSION 조회
SELECT A.MODULE, A.SQL_ID, a.machine AS MACHINE_NAME, A.* FROM V$SESSION A
 WHERE A.MODULE LIKE '%'|:MODULE|'%'
   AND A.MACHINE LIKE '%'|:MACHINE|'%'
 ORDER BY A.MACHINE
;

-- SQL_ID
SELECT
   H.SAMPLE_TIME,
   U.USERNAME,
   H.PROGRAM,
   H.MODULE,
   S.SQL_TEXT, S.*
FROM
   DBA_HIST_ACTIVE_SESS_HISTORY H,
   DBA_USERS U,
   DBA_HIST_SQLTEXT S
WHERE H.SQL_ID= '6kb4cfh0a6rak'
  AND SAMPLE_TIME BETWEEN TO_DATE(to_char(SYSDATE,'YYYYMMDD')||'000000','YYYYMMDDHH24MISS') AND TO_DATE(to_char(SYSDATE,'YYYYMMDD')||'235959','YYYYMMDDHH24MISS')
  AND H.USER_ID=U.USER_ID
  AND H.SQL_ID = S.SQL_ID
ORDER BY H.SAMPLE_TIME DESC 
;

 

수정해서 활용들 하세요.