[ORACLE] 전체 테이블, 컬럼, 코멘트 조회 / 소스 전체 내용 검색

-- 전체 테이블 조회
SELECT BB.TABLE_TYPE
           , BB.TABLE_NAME
          ,  BB.COMMENTS
  FROM ALL_ALL_TABLES AA
          , ALL_TAB_COMMENTS BB
WHERE AA.OWNER = BB.OWNER
     AND AA.TABLE_NAME = BB.TABLE_NAME
     AND AA.OWNER = ''
     AND AA.TABLE_NAME LIKE UPPER('%T%')
     AND BB.COMMENTS LIKE '%%'
   ;

-- PK 조회
SELECT AA.TABLE_NAME
           , BB.COLUMN_NAME     
           , AA.CONSTRAINT_TYPE
 FROM  ALL_CONSTRAINTS  AA
           , ALL_CONS_COLUMNS BB
WHERE 1 = 1
     AND AA.OWNER = ''
     AND AA.TABLE_NAME = ''
    AND AA.CONSTRAINT_TYPE = 'P' 
    AND AA.OWNER = BB.OWNER
    AND AA.CONSTRAINT_NAME = BB.CONSTRAINT_NAME
 ORDER BY B.POSITION
;

-- 전체 컬럼 조회
SELECT *
  FROM ALL_TAB_COLUMNS
 WHERE 1 = 1
   AND OWNER = ''
   AND TABLE_NAME LIKE '%%'
   AND COLUMN_NAME LIKE ''
  ;

-- 전체 테이블 코멘트 조회
SELECT *
  FROM ALL_COL_COMMENTS
WHERE 1 = 1
     AND OWNER = ''
     AND COLUMN_NAME = ''
     AND TABLE_NAME LIKE '%'
     AND COMMENTS LIKE '%'
;


-- 몇 분전 데이터 조회
SELECT *
  FROM 테이블
 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE)
 WHERE 조건
 ;
  
-- 전체 소스 조회
SELECT *
  FROM USER_SOURCE
  WHERE 1 = 1
    AND UPPER(TEXT) LIKE UPPER('%ROWCOUNT%')
;

-- OBJECT 조회 ( SEQUENCE, PROCEDURE 등 )
SELECT *
  FROM USER_OBJECTS
 WHERE 1 = 1
   AND OBJECT_TYPE = 'SEQUENCE'
   AND UPPER(OBJECT_NAME) LIKE UPPER('%%')
;

-- DB JOB 조회
SELECT * FROM USER_JOBS WHERE WHAT LIKE '%%';

-- KILL SESSION 확인
SELECT   DISTINCT X.SESSION_ID,  A.SERIAL#,
  D.OBJECT_NAME,  A.MACHINE,  A.TERMINAL,  A.PROGRAM,
  A.LOGON_TIME ,  'ALTER SYSTEM KILL SESSION ''' || A.SID || ',  ' || A.SERIAL# || ''';'
FROM   GV$LOCKED_OBJECT X, GV$SESSION A,  DBA_OBJECTS D
WHERE   X.SESSION_ID = A.SID  AND  X.OBJECT_ID = D.OBJECT_ID
ORDER BY LOGON_TIME
; 

-- SESSION KILL
ALTER SYSTEM KILL SESSION '';

-- 프로시져에서 처리된 SQL의 건수
N_CNT := SQL%ROWCOUNT;