[ORACLE] 스키마 이행 검증

WITH ORA_TABLE AS
(
SELECT D.TABLE_TYPE
, A.TABLE_NAME AS TBLID
, D.COMMENTS AS TBLNM
, CASE WHEN E.PARTITIONED = 'YES' THEN 'Y' END AS PARTITIONED
, E.STATUS AS STATUS
, A.COLUMN_ID AS COLNO
, A.COLUMN_NAME AS COLID
, TRIM(B.COMMENTS) AS COLNM
, C.COLUMN_POSITION AS PK
, REPLACE(A.DATA_TYPE,'TIMESTAMP(6)','TIMESTAMP') AS "TYPE"
, DECODE(A.DATA_TYPE
, 'NUMBER', NVL2(A.DATA_PRECISION, TO_CHAR(A.DATA_PRECISION)||DECODE(A.DATA_SCALE,0,NULL,','||TO_CHAR(A.DATA_SCALE)), NULL)
, 'DATE', NULL
, 'TIMESTAMP(6)', NULL
, 'TIMESTAMP', NULL
, TO_CHAR(A.DATA_LENGTH)) WIDTH
, DECODE(A.NULLABLE, 'Y', '', 'NOT NULL') "NULLS"
, A.DATA_DEFAULT AS "DEFAULT"
FROM ALL_TAB_COLUMNS A
, ALL_COL_COMMENTS B
, (
SELECT A.INDEX_OWNER AS OWNER
, A.TABLE_NAME, A.COLUMN_NAME, A.COLUMN_POSITION
FROM ALL_IND_COLUMNS A
, ALL_INDEXES B
WHERE A.INDEX_OWNER = B.OWNER
AND A.INDEX_NAME = B.INDEX_NAME
AND B.UNIQUENESS = 'UNIQUE'
AND A.INDEX_OWNER = 'SCOTT'
ORDER BY 1,3
) C
, ALL_TAB_COMMENTS D
, ALL_TABLES E
WHERE A.OWNER = 'SCOTT'
AND D.TABLE_TYPE = 'TABLE'
AND A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND A.TABLE_NAME = C.TABLE_NAME(+)
AND A.COLUMN_NAME = C.COLUMN_NAME(+)
AND A.OWNER = D.OWNER
AND A.TABLE_NAME = D.TABLE_NAME
AND A.OWNER = E.OWNER
AND A.TABLE_NAME = E.TABLE_NAME
ORDER BY D.TABLE_TYPE, A.TABLE_NAME, A.COLUMN_ID
)
-------------------------------------------------------------------------------
, ORA_INDEX AS
(
SELECT A.TABLE_OWNER AS OWNER
, A.TABLE_NAME AS TABLE_ID
, (SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE OWNER = A.TABLE_OWNER AND TABLE_NAME = A.TABLE_NAME) AS TABLE_NM
, A.UNIQUENESS
, A.INDEX_NAME
, B.COLS
, A.PARTITIONED
, CASE
WHEN A.PARTITIONED = 'YES'
THEN CASE
WHEN (SELECT COUNT(*) FROM ALL_IND_PARTITIONS WHERE INDEX_OWNER = A.OWNER AND INDEX_NAME = A.INDEX_NAME AND STATUS <> 'USABLE') >= 1
THEN 'INVALID'
ELSE 'VALID'
END
ELSE A.STATUS
END AS STATUS
, A.LAST_ANALYZED
FROM ALL_INDEXES A
LEFT JOIN
(
SELECT INDEX_OWNER
, INDEX_NAME
, SUBSTR(MAX(SYS_CONNECT_BY_PATH(COLUMN_NAME,', ')),3) AS COLS
FROM (
SELECT A.*
, ROW_NUMBER() OVER (PARTITION BY INDEX_OWNER, INDEX_NAME ORDER BY COLUMN_POSITION) AS PNO
FROM ALL_IND_COLUMNS A
WHERE INDEX_OWNER = 'SCOTT'
) A
START WITH PNO = 1
CONNECT BY PRIOR PNO = PNO - 1
AND PRIOR INDEX_OWNER = INDEX_OWNER
AND PRIOR INDEX_NAME = INDEX_NAME
GROUP BY INDEX_OWNER, INDEX_NAME
) B
ON A.OWNER = B.INDEX_OWNER
AND A.INDEX_NAME = B.INDEX_NAME
WHERE A.OWNER = 'SCOTT'
ORDER BY 1,2,3,4,5
)
-------------------------------------------------------------------------------
, ORA_VIEW AS
(
SELECT B.STATUS
, A.*
FROM ALL_VIEWS A
, ALL_OBJECTS B
WHERE A.OWNER = 'SCOTT'
AND A.OWNER = B.OWNER
AND A.VIEW_NAME = B.OBJECT_NAME
)
-------------------------------------------------------------------------------
, ORA_GRANT AS
(
SELECT GRANTEE
, TABLE_NAME
, NVL(MAX(CASE WHEN PRIVILEGE = 'SELECT' THEN 'S' END),'_')
||NVL(MAX(CASE WHEN PRIVILEGE = 'INSERT' THEN 'I' END),'_')
||NVL(MAX(CASE WHEN PRIVILEGE = 'UPDATE' THEN 'U' END),'_')
||NVL(MAX(CASE WHEN PRIVILEGE = 'DELETE' THEN 'D' END),'_')
||NVL(MAX(CASE WHEN PRIVILEGE = 'EXECUTE' THEN 'E' END),'_') AS PRIVILEGE
FROM ALL_TAB_PRIVS A
WHERE A.TABLE_SCHEMA = 'SCOTT'
GROUP BY GRANTEE
, TABLE_NAME
ORDER BY 1,2
)
-------------------------------------------------------------------------------
SELECT 'TABLE' AS OBJECT_TYPE
, COUNT(*) AS CNT
, SUM(DECODE(STATUS,'VALID',0,1)) AS INVALID_CNT
, NVL(SUM(ORA_HASH("TBLID")),0)
+NVL(SUM(ORA_HASH("TBLNM")),0)
+NVL(SUM(ORA_HASH("PARTITIONED")),0)
+NVL(SUM(ORA_HASH("COLNO")),0)
+NVL(SUM(ORA_HASH("COLID")),0)
+NVL(SUM(ORA_HASH("COLNM")),0)
+NVL(SUM(ORA_HASH("PK")),0)
+NVL(SUM(ORA_HASH("TYPE")),0)
+NVL(SUM(ORA_HASH("WIDTH")),0)
+NVL(SUM(ORA_HASH("NULLS")),0)
+SUM(NVL2("DEFAULT",100,0))
AS CHK_SUM
FROM ORA_TABLE
-------------------------------------------------------------------------------
UNION ALL
SELECT 'INDEX' AS OBJECT_TYPE
, COUNT(*) AS CNT
, SUM(DECODE(STATUS,'VALID',0,1)) AS INVALID_CNT
, NVL(SUM(ORA_HASH("OWNER")),0)
+NVL(SUM(ORA_HASH("TABLE_ID")),0)
+NVL(SUM(ORA_HASH("UNIQUENESS")),0)
+NVL(SUM(ORA_HASH("INDEX_NAME")),0)
+NVL(SUM(ORA_HASH("COLS")),0)
+NVL(SUM(ORA_HASH("PARTITIONED")),0)
AS CHK_SUM
FROM ORA_INDEX
-------------------------------------------------------------------------------
UNION ALL
SELECT 'VIEW' AS OBJECT_TYPE
, COUNT(*) AS CNT
, SUM(DECODE(STATUS,'VALID',0,1)) AS INVALID_CNT
, NVL(SUM(TEXT_LENGTH),0)
AS CHK_SUM
FROM ORA_VIEW
-------------------------------------------------------------------------------
UNION ALL
SELECT 'SEQUENCE' AS OBJECT_TYPE
, COUNT(*) AS CNT
, SUM(DECODE(B.STATUS,'VALID',0,1)) AS INVALID_CNT
, 0 AS CHK_SUM
FROM ALL_SEQUENCES A
, ALL_OBJECTS B
WHERE A.SEQUENCE_OWNER = 'SCOTT'
AND A.SEQUENCE_OWNER = B.OWNER
AND A.SEQUENCE_NAME = B.OBJECT_NAME
-------------------------------------------------------------------------------
UNION ALL
SELECT 'TYPE' AS OBJECT_TYPE
, COUNT(DISTINCT NAME) AS CNT
, COUNT(DISTINCT DECODE(STATUS,'VALID',NULL,NAME)) AS INVALID_CNT
, SUM(TEXT_LEN) AS CHK_SUM
FROM (
SELECT A.*
, LENGTHB(REPLACE(REPLACE(REPLACE(REPLACE(TEXT,' ',''),CHR(10),''),CHR(13),''),CHR(9),'')) AS TEXT_LEN
, B.STATUS
FROM ALL_SOURCE A
, ALL_OBJECTS B
WHERE A.OWNER = 'SCOTT'
AND A.OWNER = B.OWNER
AND A.NAME = B.OBJECT_NAME
AND A.TYPE = 'TYPE'
)
-------------------------------------------------------------------------------
UNION ALL
SELECT 'FUNCTION' AS OBJECT_TYPE
, COUNT(DISTINCT NAME) AS CNT
, COUNT(DISTINCT DECODE(STATUS,'VALID',NULL,NAME)) AS INVALID_CNT
, SUM(TEXT_LEN) AS CHK_SUM
FROM (
SELECT A.*
, LENGTHB(REPLACE(REPLACE(REPLACE(REPLACE(TEXT,' ',''),CHR(10),''),CHR(13),''),CHR(9),'')) AS TEXT_LEN
, B.STATUS
FROM ALL_SOURCE A
, ALL_OBJECTS B
WHERE A.OWNER = 'SCOTT'
AND A.OWNER = B.OWNER
AND A.NAME = B.OBJECT_NAME
AND A.TYPE = 'FUNCTION'
)
-------------------------------------------------------------------------------
UNION ALL
SELECT 'PROCEDURE' AS OBJECT_TYPE
, COUNT(DISTINCT NAME) AS CNT
, COUNT(DISTINCT DECODE(STATUS,'VALID',NULL,NAME)) AS INVALID_CNT
, SUM(TEXT_LEN) AS CHK_SUM
FROM (
SELECT A.*
, LENGTHB(REPLACE(REPLACE(REPLACE(REPLACE(TEXT,' ',''),CHR(10),''),CHR(13),''),CHR(9),'')) AS TEXT_LEN
, B.STATUS
FROM ALL_SOURCE A
, ALL_OBJECTS B
WHERE A.OWNER = 'SCOTT'
AND A.OWNER = B.OWNER
AND A.NAME = B.OBJECT_NAME
AND A.TYPE = 'PROCEDURE'
)
-------------------------------------------------------------------------------
UNION ALL
SELECT 'TRIGGER' AS OBJECT_TYPE
, COUNT(DISTINCT NAME) AS CNT
, COUNT(DISTINCT DECODE(STATUS,'VALID',NULL,NAME)) AS INVALID_CNT
, SUM(TEXT_LEN) AS CHK_SUM
FROM (
SELECT A.*
, LENGTHB(REPLACE(REPLACE(REPLACE(REPLACE(TEXT,' ',''),CHR(10),''),CHR(13),''),CHR(9),'')) AS TEXT_LEN
, B.STATUS
FROM ALL_SOURCE A
, ALL_OBJECTS B
WHERE A.OWNER = 'SCOTT'
AND A.OWNER = B.OWNER
AND A.NAME = B.OBJECT_NAME
AND A.TYPE = 'TRIGGER'
)
-------------------------------------------------------------------------------
UNION ALL
SELECT 'ROLE' AS OBJECT_TYPE
, COUNT(*) AS CNT
, 0 AS INVALID_CNT
, 0 AS CHK_SUM
FROM DBA_ROLES
WHERE ROLE LIKE '%SCOTT%'
-------------------------------------------------------------------------------
UNION ALL
SELECT 'GRANT' AS OBJECT_TYPE
, COUNT(*) AS CNT
, 0 AS INVALID_CNT
, NVL(SUM(ORA_HASH("GRANTEE")),0)
+NVL(SUM(ORA_HASH("TABLE_NAME")),0)
+NVL(SUM(ORA_HASH("PRIVILEGE")),0)
AS CHK_SUM
FROM ORA_GRANT
-------------------------------------------------------------------------------
UNION ALL
SELECT 'SYNONYM' AS OBJECT_TYPE
, COUNT(*) AS CNT
, SUM(DECODE(STATUS,'VALID',0,1)) AS INVALID_CNT
, 0 AS CHK_SUM
FROM (
SELECT A.*
, B.STATUS
FROM ALL_SYNONYMS A
, ALL_OBJECTS B
WHERE A.TABLE_OWNER = 'SCOTT'
AND B.OWNER = 'PUBLIC'
AND A.TABLE_NAME = B.OBJECT_NAME
)
;