스키마 조회 쿼리 모음

SELECT A.TABLE_NAME
,(SELECT C.COMMENTS FROM DBA_TAB_COMMENTS C WHERE A.OWNER = C.OWNER AND A.TABLE_NAME = C.TABLE_NAME) AS TABLE_COMMENTS
,B.COLUMN_ID AS COLUMN_NO
,B.COLUMN_NAME
,(SELECT D.COMMENTS FROM DBA_COL_COMMENTS D WHERE A.OWNER = D.OWNER AND A.TABLE_NAME = D.TABLE_NAME AND B.COLUMN_NAME = D.COLUMN_NAME) AS COLUMN_COMMENTS
,B.DATA_TYPE
,CASE
WHEN B.DATA_TYPE IN ('VARCHAR', 'VARCHAR2', 'CHAR') THEN
TO_CHAR(B.DATA_LENGTH)
WHEN B.DATA_TYPE = 'NUMBER' THEN
B.DATA_PRECISION || ',' || DECODE(B.DATA_SCALE,0,'0',B.DATA_SCALE)
ELSE ''
END AS DATA_LENGTH
,B.NULLABLE
FROM DBA_TABLES A
JOIN DBA_TAB_COLUMNS B ON A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
WHERE A.OWNER = 'EDUIS'
AND (A.TABLE_NAME LIKE 'A______T' OR A.TABLE_NAME LIKE 'Y_BANK_%')
ORDER BY A.TABLE_NAME
,B.COLUMN_ID

/*
※ DB 스키마 조회
1. DBA권한을 가진 스키마로 DB 접속 후
2. OBJECT 관리 스키마 목록을 "(<스키마목록>)"에 열거한 후 SQL을 수행한다.
예) OWNER IN ('AAA', 'BBB', 'CCC')
3. 쿼리 결과를 CSV 파일 또는 Excel 파일로 저장 한다.
저장 파일명은 시스템명_쿼리명.csv
예) 학자금_01_컬럼목록.csv
*/

/*
쿼리명 : 01_컬럼목록
설 명 : Oracle Dictionary에서 컬럼의 Comment 와 Type, Null 여부를 조회.
*/
SELECT
A.OWNER
, A.TABLE_NAME
, A.COLUMN_NAME
, A.COLUMN_ID AS COL_ORDER
, CASE WHEN A.DATA_TYPE IN ('VARCHAR', 'VARCHAR2', 'CHAR') THEN
A.DATA_TYPE || '(' || A.DATA_LENGTH || ')'
WHEN A.DATA_TYPE = 'NUMBER' THEN
A.DATA_TYPE || '(' || A.DATA_PRECISION || ',' || DECODE(A.DATA_SCALE,0,'0',A.DATA_SCALE) || ')'
ELSE A.DATA_TYPE
END AS FULL_DATA_TYPE
, A.DATA_TYPE
, A.DATA_LENGTH
, A.DATA_PRECISION
, A.DATA_SCALE
, A.NULLABLE
, REPLACE(B.COMMENTS, CHR(10), '') AS COMMENTS
FROM DBA_TAB_COLUMNS A
, DBA_COL_COMMENTS B
, DBA_TABLES C
WHERE A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
--A.OWNER IN (<스키마목록>)
AND A.OWNER = C.OWNER
AND A.TABLE_NAME = C.TABLE_NAME
AND A.OWNER = B.OWNER(+)
AND A.TABLE_NAME = B.TABLE_NAME(+)
AND A.COLUMN_NAME = B.COLUMN_NAME(+)
;

/*
쿼리명 : 02_제약사항
설 명 : Oracle Dictionary에서 PK를 포함한 Constraint 컬럼 조회
Constraint Type
P : Primary
U : Unique Index
C : Check
R : Relation Ship (Forgin Key)
*/
SELECT A.OWNER
, A.TABLE_NAME
, A.CONSTRAINT_TYPE
, A.CONSTRAINT_NAME
, DECODE(A.STATUS, 'ENABLED', 'Y', NULL) ENABLED
, B.COLUMN_NAME
, B.POSITION
, A.R_OWNER
, A.R_CONSTRAINT_NAME
FROM DBA_CONSTRAINTS A
, DBA_CONS_COLUMNS B
WHERE A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
--A.OWNER IN (<스키마목록>)
AND A.CONSTRAINT_TYPE IN ('P','U','R')
AND A.OWNER = B.OWNER
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
;

/*
쿼리명 : 03_인덱스
설 명 : Oracle Dictionary에서 INDEX 컬럼을 조회
*/
SELECT A.INDEX_OWNER
, A.INDEX_NAME
, A.TABLE_OWNER
, A.TABLE_NAME
, A.COLUMN_NAME
, A.COLUMN_POSITION
, A.COLUMN_LENGTH
, A.CHAR_LENGTH
, A.DESCEND
, B.CONSTRAINT_TYPE
FROM DBA_IND_COLUMNS A
, DBA_CONSTRAINTS B
WHERE A.INDEX_OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
--A.INDEX_OWNER IN (<스키마목록>)
AND A.INDEX_OWNER = B.OWNER (+)
AND A.INDEX_NAME = B.CONSTRAINT_NAME (+)
;

/*
쿼리명 : 04_테이블목록
설 명 : Oracle Dictionary에서 테이블 목록 및 테이블 코멘트를 조회
*/
SELECT A.*
,B.*
FROM DBA_TABLES A
,DBA_TAB_COMMENTS B
WHERE A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
--A.OWNER IN (<스키마목록>)
AND A.OWNER = B.OWNER (+)
AND A.TABLE_NAME = B.TABLE_NAME (+)
--AND B.COMMENTS (+) IS NOT NULL
;

/*
쿼리명 : 04-1_Owner별 테이블 수
설 명 : Oracle Dictionary에서 Owner별 테이블 수를 조회
*/
SELECT OWNER, COUNT(*) CNT
FROM DBA_TABLES A
WHERE A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
--A.OWNER IN (<스키마목록>)
GROUP BY OWNER

/*
쿼리명 : 05_FK키
설 명 : Oracle Dictionary에서 FK Constraint 참조 테이블 및 컬럼 조회
*/
SELECT A.CONSTRAINT_NAME
,A.OWNER
,A.TABLE_NAME
,A.PK_OWNER
,A.PK_TABLE
,A.CONSTRAINT_TYPE
,MAX(CASE WHEN B.POSITION IS NULL OR B.POSITION=1 THEN B.COLUMN_NAME END) COLUMN01
,MAX(CASE WHEN B.POSITION= 2 THEN B.COLUMN_NAME END) COLUMN02
,MAX(CASE WHEN B.POSITION= 3 THEN B.COLUMN_NAME END) COLUMN03
,MAX(CASE WHEN B.POSITION= 4 THEN B.COLUMN_NAME END) COLUMN04
,MAX(CASE WHEN B.POSITION= 5 THEN B.COLUMN_NAME END) COLUMN05
,MAX(CASE WHEN B.POSITION= 6 THEN B.COLUMN_NAME END) COLUMN06
,MAX(CASE WHEN B.POSITION= 7 THEN B.COLUMN_NAME END) COLUMN07
,MAX(CASE WHEN B.POSITION= 8 THEN B.COLUMN_NAME END) COLUMN08
,MAX(CASE WHEN B.POSITION= 9 THEN B.COLUMN_NAME END) COLUMN09
,MAX(CASE WHEN B.POSITION=10 THEN B.COLUMN_NAME END) COLUMN10
FROM (
SELECT A.CONSTRAINT_NAME
,A.OWNER AS OWNER
,A.TABLE_NAME AS TABLE_NAME
,A.R_OWNER AS PK_OWNER
,B.TABLE_NAME AS PK_TABLE
,A.CONSTRAINT_TYPE
,DECODE(A.STATUS, 'ENABLED', 'Y', NULL) ENABLED
FROM DBA_CONSTRAINTS A
,DBA_CONSTRAINTS B
WHERE A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
-- A.OWNER IN (<스키마목록>)
AND A.CONSTRAINT_TYPE IN ('P','R')
AND A.R_OWNER = B.OWNER(+)
AND A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME(+)
) A
, ALL_CONS_COLUMNS B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
GROUP BY A.OWNER, A.TABLE_NAME, A.PK_OWNER, A.PK_TABLE, A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE
;

/*
쿼리명 : 06_레코드건수
설 명 : 테이블별 레코드건수 및 사이즈를 조회
※ ANALYZE 를 수행한 테이블만 용량산정이 가능함
*/
WITH
TM_TB_SIZE AS (
SELECT SEGMENT_NAME AS TABLE_NAME
, BYTES
, (BYTES/1024)/1024 AS TABLE_SIZE
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
AND OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
-- OWNER IN (<스키마목록>)
)
SELECT A.OWNER
, A.TABLE_NAME
, A.TABLESPACE_NAME
, A.NUM_ROWS
, A.BLOCKS
, A.LAST_ANALYZED
, A.PARTITIONED
, B.BYTES AS "TABLE_SIZE(BYTE)"
, B.TABLE_SIZE AS "TABLE_SIZE(MB)"
FROM DBA_TABLES A
, TM_TB_SIZE B
WHERE A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
--A.OWNER IN (<스키마목록>)
AND A.TABLE_NAME = B.TABLE_NAME (+)
;

/*
쿼리명 : 07_테이블스페이스용량
설 명 : 대략적인 테이블스페이스별 할당량, 사용량을 조회 한다.
*/
SELECT A.TABLESPACE_NAME
, ROUND(A.BYTES / 1024 / 1024) AS "MB ALLOCATED"
, ROUND((A.BYTES-NVL(B.BYTES, 0)) / 1024 / 1024) AS "MB USED"
, NVL(ROUND(B.BYTES / 1024 / 1024), 0) AS "MB FREE"
, ROUND(((A.BYTES-NVL(B.BYTES, 0))/A.BYTES)*100,2) AS "PCT USED"
, ROUND((1-((A.BYTES-NVL(B.BYTES,0))/A.BYTES))*100,2) AS "PCT FREE"
FROM ( SELECT TABLESPACE_NAME
, SUM(BYTES) BYTES
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) A,
( SELECT TABLESPACE_NAME
, SUM(BYTES) BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
;

/*
쿼리명 : 08_오브젝트용량
설 명 : 대략적인 테이블스페이스내 오브젝트의 사용량을 조회 한다.
*/
SELECT S.OWNER
, SUBSTR(S.SEGMENT_NAME, 1, 30) AS "TABLE NAME"
, ROUND(SUM(S.BYTES) / 1024 / 1024, 2) AS "MB ALLOCATED"
, CASE WHEN T.BLOCKS > 0 THEN
ROUND(SUM(S.BYTES) / 1024 / 1024 * TO_NUMBER(DECODE(T.BLOCKS, NULL, NULL, (T.BLOCKS / (T.BLOCKS + T.EMPTY_BLOCKS)))), 2)
ELSE 0
END AS "MB USED"
, CASE WHEN T.BLOCKS > 0 THEN
TO_NUMBER(DECODE(T.BLOCKS,NULL, NULL, ROUND(100 *(T.BLOCKS / (T.BLOCKS + T.EMPTY_BLOCKS)), 2)))
ELSE 0
END AS "PCT FULL"
, S.TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS S
, SYS.DBA_TABLES T
WHERE T.OWNER = S.OWNER
AND T.TABLE_NAME = S.SEGMENT_NAME
AND T.TABLESPACE_NAME = S.TABLESPACE_NAME
AND S.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
-- S.OWNER IN (<스키마목록>)
GROUP BY S.TABLESPACE_NAME, S.OWNER, S.SEGMENT_NAME, S.SEGMENT_TYPE, T.BLOCKS, T.EMPTY_BLOCKS