PostgreSQL: PG ↔ Oracle 이관 시 메타데이터 정합성 체크 쿼리

※ Metadata integrity check methods during migration from PostgreSQL ↔ Oracle.

---

PostgreSQL 체크용 쿼리

-- 1.스키마별 디스크 사용량
SELECT schema_name, pg_size_pretty(sum(table_size)::bigint) as disk_space
FROM (SELECT pg_catalog.pg_namespace.nspname as schema_name, pg_relation_size(pg_catalog.pg_class.oid) as table_size 
      FROM pg_catalog.pg_class 
      JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid 
      WHERE pg_catalog.pg_namespace.nspname NOT IN ('information_schema', 'pg_catalog')) t -- 제외할 스키마 조건
GROUP BY schema_name ORDER BY schema_name;


-- 2.스키마별 객체 개수
SELECT
n.nspname as schema_name,
CASE c.relkind
    WHEN 'r' THEN 'table' -- 테이블
    WHEN 'v' THEN 'view' -- 뷰
    WHEN 'i' THEN 'index' -- 인덱스
    WHEN 'S' THEN 'sequence' -- 시퀀스
    WHEN 's' THEN 'special' -- 특별 객체
    WHEN 'm' THEN 'materialized view' -- 물리화 뷰
    WHEN 'p' THEN 'partitioned table' -- 파티션 테이블
    WHEN 'f' THEN 'foreign table' -- 외부 테이블
    WHEN 'c' THEN 'composite type' -- 복합 타입
END as object_type,
count(1) as object_count
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','i','S','s','m','p','f','c')
AND n.nspname NOT IN ('information_schema', 'pg_catalog') -- 제외할 스키마 조건
GROUP BY  n.nspname,
CASE c.relkind
    WHEN 'r' THEN 'table'
    WHEN 'v' THEN 'view'
    WHEN 'i' THEN 'index'
    WHEN 'S' THEN 'sequence'
    WHEN 's' THEN 'special'
    WHEN 'm' THEN 'materialized view'
    WHEN 'p' THEN 'partitioned table'
    WHEN 'f' THEN 'foreign table'
    WHEN 'c' THEN 'composite type'
END
ORDER BY n.nspname,
CASE c.relkind
    WHEN 'r' THEN 'table'
    WHEN 'v' THEN 'view'
    WHEN 'i' THEN 'index'
    WHEN 'S' THEN 'sequence'
    WHEN 's' THEN 'special'
    WHEN 'm' THEN 'materialized view'
    WHEN 'p' THEN 'partitioned table'
    WHEN 'f' THEN 'foreign table'
    WHEN 'c' THEN 'composite type'
END;


-- 3.데이터 정합성 체크: PK, FK 및 UNIQUE 제약조건
SELECT constraint_schema, constraint_type, count(*)
FROM information_schema.table_constraints
WHERE constraint_schema NOT IN ('information_schema', 'pg_catalog') -- 제외할 스키마 조건
AND constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE') -- PRIMARY KEY, FOREIGN KEY, UNIQUE 제약조건
GROUP BY constraint_schema, constraint_type
ORDER BY constraint_schema, constraint_type;


-- 4.테이블별 행 개수
SELECT schemaname AS schema_name, tablename AS table_name, (xpath('/row/cnt/text()', xml_count))[1]::text::int AS row_count
FROM (SELECT schemaname, tablename, query_to_xml('SELECT count(*) as cnt FROM ' || schemaname || '.' || tablename, false, true, '') as xml_count 
      FROM pg_tables 
      WHERE schemaname NOT IN ('information_schema', 'pg_catalog')) -- 제외할 스키마 조건
      as subquery; 


-- 5.인덱스 이름 및 크기
SELECT schemaname AS schema_name, indexname AS index_name, pg_size_pretty(pg_relation_size(c.oid)) AS index_size
FROM pg_indexes i
JOIN pg_class c ON i.indexname = c.relname
WHERE i.schemaname NOT IN ('information_schema', 'pg_catalog') -- 제외할 스키마 조건
ORDER BY i.schemaname, i.indexname;


-- 6.스키마별 시퀀스 마지막 값
SELECT schemaname, sequencename, last_value
FROM pg_catalog.pg_sequences
WHERE schemaname NOT IN ('information_schema', 'pg_catalog') -- 제외할 스키마 조건
ORDER BY schemaname, sequencename;


-- 7.스키마별 UDF 및 PL/pgSQL 함수 개수
SELECT pronamespace::regnamespace AS schema_name,
    CASE WHEN prokind = 'f' THEN 'Normal Function' -- 일반 함수
         WHEN prokind = 'p' THEN 'Procedure' -- 프로시저
         WHEN prokind = 'a' THEN 'Aggregate Function' -- 집계 함수
         WHEN prokind = 'w' THEN 'Window Function' -- 윈도우 함수
    END AS function_type, COUNT(*) AS function_count
FROM pg_proc
WHERE pronamespace::regnamespace NOT IN ('information_schema', 'pg_catalog')
GROUP BY pronamespace, prokind
ORDER BY pronamespace, prokind;


-- 8.UDF 및 PL/pgSQL 함수 목록
SELECT pronamespace::regnamespace AS schema_name,
    CASE WHEN prokind = 'f' THEN 'Normal Function' -- 일반 함수
         WHEN prokind = 'p' THEN 'Procedure' -- 프로시저
         WHEN prokind = 'a' THEN 'Aggregate Function' -- 집계 함수
         WHEN prokind = 'w' THEN 'Window Function' -- 윈도우 함수
    END AS function_type, proname AS object_name
FROM pg_proc
WHERE pronamespace::regnamespace NOT IN ('information_schema', 'pg_catalog') -- 제외할 스키마 조건
ORDER BY pronamespace, prokind;


-- 9.스키마별 트리거 개수
SELECT event_object_schema AS schema_name, COUNT(*) AS trigger_count
FROM information_schema.triggers
WHERE trigger_schema NOT IN ('information_schema', 'pg_catalog') -- 제외할 스키마 조건
GROUP BY event_object_schema;


-- 10.트리거 목록
SELECT event_object_schema AS schema_name, trigger_name
FROM information_schema.triggers
WHERE trigger_schema NOT IN ('information_schema', 'pg_catalog') -- 제외할 스키마 조건
ORDER BY event_object_schema, trigger_name;

---

Oracle 체크용 쿼리

-- 1.Schema별 디스크 사용량
SELECT OWNER AS SCHEMA_NAME,
       TO_CHAR(SUM(BYTES) / (1024 * 1024), 'FM999,999,999') || ' MB' AS DISK_SPACE
FROM DBA_SEGMENTS
WHERE OWNER NOT IN ('SYS', 'SYSTEM') -- 제외할 스키마 조건
GROUP BY OWNER
ORDER BY OWNER;


-- 2.Schema별 객체 개수
SELECT OWNER AS SCHEMA_NAME,
       OBJECT_TYPE AS OBJECT_TYPE,
       COUNT(*) AS OBJECT_COUNT
FROM DBA_OBJECTS
WHERE OWNER NOT IN ('SYS', 'SYSTEM') -- 제외할 스키마 조건
GROUP BY OWNER, OBJECT_TYPE
ORDER BY OWNER, OBJECT_TYPE;


-- 3.데이터 정합성 체크: PK, FK 및 UNIQUE 제약조건
SELECT OWNER AS CONSTRAINT_SCHEMA,
       CONSTRAINT_TYPE,
       COUNT(*) AS CONSTRAINT_COUNT
FROM DBA_CONSTRAINTS
WHERE OWNER NOT IN ('SYS', 'SYSTEM') -- 제외할 스키마 조건
AND CONSTRAINT_TYPE IN ('P', 'R', 'U') -- P: PRIMARY KEY, R: FOREIGN KEY, U: UNIQUE
GROUP BY OWNER, CONSTRAINT_TYPE
ORDER BY OWNER, CONSTRAINT_TYPE;


-- 4.테이블별 행 개수
SELECT OWNER AS SCHEMA_NAME,
       TABLE_NAME,
       NUM_ROWS AS ROW_COUNT
FROM DBA_TABLES
WHERE OWNER NOT IN ('SYS', 'SYSTEM') -- 제외할 스키마 조건
ORDER BY OWNER, TABLE_NAME;


-- 5.인덱스 이름 및 크기
SELECT
    i.owner AS schema_name,       -- 스키마 이름
    i.index_name AS index_name,  -- 인덱스 이름
    TO_CHAR(SUM(s.bytes) / (1024 * 1024), 'FM999,999,999') || ' MB' AS index_size -- 인덱스 크기(MB)
FROM
    dba_indexes i
JOIN
    dba_segments s
ON
    i.index_name = s.segment_name
    AND i.owner = s.owner
WHERE
    i.owner NOT IN ('SYS', 'SYSTEM') -- 제외할 스키마 조건
    AND s.segment_type = 'INDEX'
GROUP BY
    i.owner, i.index_name
ORDER BY
    i.owner, i.index_name;


-- 6.Sequence 마지막 값
SELECT SEQUENCE_OWNER AS SCHEMA_NAME,
       SEQUENCE_NAME,
       LAST_NUMBER AS LAST_VALUE
FROM DBA_SEQUENCES
WHERE SEQUENCE_OWNER NOT IN ('SYS', 'SYSTEM') -- 제외할 스키마 조건
ORDER BY SEQUENCE_OWNER, SEQUENCE_NAME;


-- 7.스키마별 UDF 및 PL/SQL 함수 개수
SELECT
    OWNER AS schema_name, -- 스키마 이름
    DECODE(
        OBJECT_TYPE,
        'FUNCTION', 'Normal Function',
        'PROCEDURE', 'Procedure',
        OBJECT_TYPE
    ) AS function_type, -- 함수 유형
    COUNT(*) AS function_count -- 함수 개수
FROM
    DBA_OBJECTS
WHERE
    OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE') -- 함수 또는 프로시저만 조회
    AND OWNER NOT IN ('SYS', 'SYSTEM') -- 제외할 스키마 조건
GROUP BY
    OWNER,
    DECODE(
        OBJECT_TYPE,
        'FUNCTION', 'Normal Function',
        'PROCEDURE', 'Procedure',
        OBJECT_TYPE
    )
ORDER BY
    OWNER,
    function_type;


-- 8.스키마별 UDF 및 PL/SQL 함수 목록
SELECT
    OWNER AS schema_name, -- 스키마 이름
    DECODE(
        OBJECT_TYPE,
        'FUNCTION', 'Normal Function',
        'PROCEDURE', 'Procedure',
        OBJECT_TYPE
    ) AS function_type, -- 함수 유형
    OBJECT_NAME AS object_name -- 객체 이름
FROM
    DBA_OBJECTS
WHERE
    OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE') -- 함수 또는 프로시저만 조회
    AND OWNER NOT IN ('SYS', 'SYSTEM') -- 제외할 스키마 조건
ORDER BY
    OWNER,
    function_type,
    OBJECT_NAME;


-- 9.스키마별 트리거 개수
SELECT OWNER AS SCHEMA_NAME,
       COUNT(*) AS TRIGGER_COUNT
FROM DBA_TRIGGERS
WHERE OWNER NOT IN ('SYS', 'SYSTEM') -- 제외할 스키마 조건
GROUP BY OWNER;


-- 10.트리거 목록
SELECT OWNER AS SCHEMA_NAME,
       TRIGGER_NAME
FROM DBA_TRIGGERS
WHERE OWNER NOT IN ('SYS', 'SYSTEM') -- 제외할 스키마 조건
ORDER BY OWNER, TRIGGER_NAME;

---

잘 활용해 주세요-!

 

오늘은 여기까지 :)

다음 포스팅에서 뵙겠습니다.