-- 통계정보수집
ANALYZE TABLE web_user.web_menu COMPUTE STATISTICS;
-- row 평균 size
SELECT GREATEST(4, CEIL(NUM_ROWS/((ROUND(((1958-(INI_TRANS*23))*((100-PCT_FREE)/100))/AVG_ROW_LEN)))) * 2048) TABLESIZE_KBYTES FROM USER_TABLES WHERE TABLE_NAME = 'WEB_BLACKLISTIP';
-- 테이블 row size
select table_name, sum(data_length) from user_tab_columns group by table_name order by table_name asc;
-- 테이블 별 row size
select ut.table_name
, utc.comments
, ts.bytes
, num_rows
, 0
, 0
from user_Tables ut
join user_tab_comments utc on ut.table_name = utc.table_name
join (select table_name, sum(data_length) as bytes from user_tab_columns group by table_name) ts on ut.table_name = ts.table_name
order by ut.table_name asc
-- 제약조건조회
select constraint_name, constraint_type, table_name, r_constraint_name, delete_rule, index_name from user_constraints where constraint_type != 'C' order by table_name asc;
select constraint_name, table_name, column_name from user_cons_columns order by table_name asc, column_name asc;
1. Table Comment
SELECT TAB.TABLE_NAME
, TAB.COMMENTS
FROM USER_TAB_COMMENTS TAB
WHERE TAB.TABLE_TYPE = 'TABLE';
2. Column Comment
SELECT CMT.TABLE_NAME
, CMT.COLUMN_NAME
, CMT.COMMENTS
FROM USER_COL_COMMENTS CMT;
3. Table Column Mapping Table
SELECT COL.TABLE_NAME
, COL.COLUMN_NAME
, COL.DATA_TYPE
, COL.DATA_LENGTH
, COL.NULLABLE
, COL.DATA_DEFAULT
, COL.COLUMN_ID
FROM USER_TAB_COLUMNS COL;
4. Table/Column Comment (테이블-컬럼)
SELECT DECODE(LAG(COL.TABLE_NAME, 1, NULL) OVER (PARTITION BY COL.TABLE_NAME ORDER BY COL.TABLE_NAME ASC, COL.COLUMN_ID ASC), NULL, COL.TABLE_NAME, ' ') AS TABLE_NAME
, DECODE(LAG(TAB.COMMENTS, 1, NULL) OVER (PARTITION BY TAB.COMMENTS ORDER BY COL.TABLE_NAME ASC, COL.COLUMN_ID ASC), NULL, TAB.COMMENTS, ' ') AS TABLE_COMMENT
, COL.COLUMN_NAME
, COL.DATA_TYPE
, COL.DATA_LENGTH
, COL.DATA_PRECISION
, DECODE(COL.NULLABLE, 'N', 'NOT NULL', 'Y', 'NULL') AS NULLABLE
, COL.DATA_DEFAULT
, COL.COLUMN_ID
, CMT.COMMENTS AS COLUMN_COMMENT
, NVL(CST.PK, ' ') AS PK
, NVL(TO_CHAR(CST.PK_POSITION), ' ') AS PK_POSITION
, NVL(CST.FK, ' ') AS FK
, NVL(TO_CHAR(CST.FK_POSITION), ' ') AS FK_POSITION
FROM USER_TAB_COLUMNS COL
JOIN USER_TAB_COMMENTS TAB ON TAB.TABLE_NAME = COL.TABLE_NAME
JOIN USER_COL_COMMENTS CMT ON CMT.TABLE_NAME = COL.TABLE_NAME
AND CMT.COLUMN_NAME = COL.COLUMN_NAME
LEFT OUTER JOIN (SELECT CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME
, MIN(DECODE(CST.CONSTRAINT_TYPE, 'P', CTC.CONSTRAINT_NAME)) AS PK
, MIN(DECODE(CST.CONSTRAINT_TYPE, 'P', CTC.POSITION)) AS PK_POSITION
, MIN(DECODE(CST.CONSTRAINT_TYPE, 'R', CTC.CONSTRAINT_NAME)) AS FK
, MIN(DECODE(CST.CONSTRAINT_TYPE, 'R', CTC.POSITION)) AS FK_POSITION
FROM USER_CONS_COLUMNS CTC
JOIN USER_CONSTRAINTS CST ON CTC.OWNER = CST.OWNER
AND CTC.TABLE_NAME = CST.TABLE_NAME
AND CTC.CONSTRAINT_NAME = CST.CONSTRAINT_NAME
WHERE CST.CONSTRAINT_TYPE != 'C'
GROUP BY CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME) CST ON CST.TABLE_NAME = COL.TABLE_NAME
AND CST.COLUMN_NAME = COL.COLUMN_NAME
WHERE TAB.TABLE_TYPE = 'TABLE'
ORDER BY COL.TABLE_NAME ASC
, COL.COLUMN_ID ASC;
5. Table 정의서
CREATE OR REPLACE FUNCTION FNC_LONG2VARCHAR2 ( TABLE_NAME VARCHAR2
, COLUMN_NAME VARCHAR2) RETURN VARCHAR2
IS
V_RTN VARCHAR2(32767);
V_SQL VARCHAR2(2000);
BEGIN
V_SQL := 'SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ''' || TABLE_NAME || ''' AND COLUMN_NAME = ''' || COLUMN_NAME || '''';
EXECUTE IMMEDIATE V_SQL INTO V_RTN;
V_RTN := SUBSTR (V_RTN, 1, 4000);
RETURN V_RTN;
END FNC_LONG2VARCHAR2;
SELECT COL01
, COL02
, COL03
, COL04
, COL05
, COL06
, COL07
, COL08
, COL09
, COL10
FROM (SELECT TABLE_NAME AS TABLE_NAME
, NO AS GROUP_NO
, 1 AS ORDER_NO
, DECODE(NO, 2, '시스템명', 3, '테이블ID', 4, '테이블명', 5, 'NO') AS COL01
, DECODE(NO, 2, 'SIS', 3, TABLE_NAME, 4, COMMENTS, 5, '컬럼ID') AS COL02
, DECODE(NO, 5, '컬럼명') AS COL03
, DECODE(NO, 5, 'KEY') AS COL04
, DECODE(NO, 2, '작성일', 3, '작성자', 4, NULL, 5, 'TYPE') AS COL05
, DECODE(NO, 5, '길이') AS COL06
, DECODE(NO, 2, TO_CHAR(SYSDATE, 'YYYY.MM.DD'), 3, '김모씨', 4, NULL, 5, 'BYTE') AS COL07
, DECODE(NO, 5, 'NULLABLE') AS COL08
, DECODE(NO, 5, 'DEFAULT') AS COL09
, DECODE(NO, 5, '특성') AS COL10
FROM (SELECT T.TABLE_NAME
, T.COMMENTS
, D.NO
FROM USER_TAB_COMMENTS T
, (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 5) D
WHERE T.TABLE_TYPE = 'TABLE')
UNION ALL
SELECT COL.TABLE_NAME AS TABLE_NAME
, 6 AS GROUP_NO
, COL.COLUMN_ID AS ORDER_NO
, TO_CHAR(COL.COLUMN_ID) AS COL01
, COL.COLUMN_NAME AS COL02
, CMT.COMMENTS AS COL03
, DECODE(CST.PK, NULL, ' ', 'PK') AS COL04
, COL.DATA_TYPE AS COL05
, CASE
WHEN COL.DATA_TYPE = 'NUMBER' AND COL.DATA_PRECISION IS NOT NULL THEN '(' || COL.DATA_PRECISION || ', ' || COL.DATA_SCALE || ')'
WHEN COL.DATA_TYPE IN ('CHAR', 'VARCHAR2') THEN '(' || COL.CHAR_LENGTH || DECODE(COL.CHAR_USED, 'B', ' BYTE', 'C', ' CHAR') || ')'
ELSE ' '
END AS COL06
, TO_CHAR(COL.DATA_LENGTH) AS COL07
, DECODE(COL.NULLABLE, 'N', 'NOT NULL', 'Y', 'NULL') AS COL08
--, COL.DATA_DEFAULT
, TRIM(PKG_MGM.FNC_LONG2VARCHAR2(COL.TABLE_NAME, COL.COLUMN_NAME)) AS COL09
, DECODE(CST.FK, NULL, ' ', 'FK') AS COL10
FROM USER_TAB_COLUMNS COL
JOIN USER_TAB_COMMENTS TAB ON TAB.TABLE_NAME = COL.TABLE_NAME
JOIN USER_COL_COMMENTS CMT ON CMT.TABLE_NAME = COL.TABLE_NAME
AND CMT.COLUMN_NAME = COL.COLUMN_NAME
LEFT OUTER JOIN (SELECT CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME
, MIN(DECODE(CST.CONSTRAINT_TYPE, 'P', CTC.CONSTRAINT_NAME)) AS PK
, MIN(DECODE(CST.CONSTRAINT_TYPE, 'P', CTC.POSITION)) AS PK_POSITION
, MIN(DECODE(CST.CONSTRAINT_TYPE, 'R', CTC.CONSTRAINT_NAME)) AS FK
, MIN(DECODE(CST.CONSTRAINT_TYPE, 'R', CTC.POSITION)) AS FK_POSITION
FROM USER_CONS_COLUMNS CTC
JOIN USER_CONSTRAINTS CST ON CTC.OWNER = CST.OWNER
AND CTC.TABLE_NAME = CST.TABLE_NAME
AND CTC.CONSTRAINT_NAME = CST.CONSTRAINT_NAME
WHERE CST.CONSTRAINT_TYPE != 'C'
GROUP BY CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME) CST ON CST.TABLE_NAME = COL.TABLE_NAME
AND CST.COLUMN_NAME = COL.COLUMN_NAME
WHERE TAB.TABLE_TYPE = 'TABLE')
ORDER BY TABLE_NAME
, GROUP_NO
, ORDER_NO;