Oracle에서 PostgreSQL로 이행

export ORACLE_SID=<SOURCE_DB_NAME>
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PGPASSWORD=<PASSWORD>
export PGCLIENTENCODING=UTF8
mkdir -p /home/oracle/mig/pg/data
cd /home/oracle/mig/pg
which psql || ls /usr/pgsql-*/bin/psql

SET PAGESIZE 0
SET LINESIZE 32767
SET LONG 100000
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET TAB OFF

@/home/oracle/mig/pg/set_gen.sql
SPOOL /home/oracle/mig/pg/pg_01_tables.sql
SELECT 'DROP TABLE IF EXISTS ' || LOWER(t.table_name) || ' CASCADE;' || CHR(10) ||
       'CREATE TABLE ' || LOWER(t.table_name) || ' (' || CHR(10) ||
       LISTAGG('  ' || RPAD(LOWER(c.column_name), 12) || ' ' ||
         CASE
           WHEN c.data_type = 'NUMBER' AND c.data_precision IS NULL THEN 'NUMERIC'
           WHEN c.data_type = 'NUMBER' AND c.data_scale = 0        THEN 'NUMERIC(' || c.data_precision || ')'
           WHEN c.data_type = 'NUMBER'                             THEN 'NUMERIC(' || c.data_precision || ',' || c.data_scale || ')'
           WHEN c.data_type IN ('VARCHAR2','NVARCHAR2')            THEN 'VARCHAR(' || c.char_length || ')'
           WHEN c.data_type IN ('CHAR','NCHAR')                    THEN 'CHAR(' || c.char_length || ')'
           WHEN c.data_type = 'DATE'                               THEN 'TIMESTAMP(0)'
           WHEN c.data_type LIKE 'TIMESTAMP%TIME ZONE'             THEN 'TIMESTAMPTZ'
           WHEN c.data_type LIKE 'TIMESTAMP%'                      THEN 'TIMESTAMP'
           WHEN c.data_type = 'FLOAT'                              THEN 'DOUBLE PRECISION'
           WHEN c.data_type = 'CLOB'                               THEN 'TEXT'
           WHEN c.data_type = 'BLOB'                               THEN 'BYTEA'
           ELSE 'TEXT /* 확인 필요: ' || c.data_type || ' */'
         END ||
         CASE WHEN c.nullable = 'N' THEN ' NOT NULL' END,
       ',' || CHR(10)) WITHIN GROUP (ORDER BY c.column_id)
       || CHR(10) || ');' || CHR(10)
  FROM user_tables t
  JOIN user_tab_columns c ON c.table_name = t.table_name
 WHERE t.table_name <> 'MIG_CHK'
   AND t.dropped = 'NO'
 GROUP BY t.table_name
 ORDER BY t.table_name;
SPOOL OFF
EXIT

sqlplus -s <USERNAME>/<PASSWORD> @gen_ddl.sql
cat pg_01_tables.sql
psql -h localhost -U <USERNAME> -d <TARGET_DB_NAME> -v ON_ERROR_STOP=1 -f pg_01_tables.sql
psql -h localhost -U <USERNAME> -d <TARGET_DB_NAME> -c '\dt'

-- gen_export.sql: 테이블마다 SPOOL → SELECT * → SPOOL OFF 명령 생성
@/home/oracle/mig/pg/set_gen.sql
SPOOL /home/oracle/mig/pg/ora_export.sql
SELECT 'SPOOL /home/oracle/mig/pg/data/' || LOWER(table_name) || '.csv' || CHR(10) ||
       'SELECT * FROM ' || table_name || ';' || CHR(10) || 'SPOOL OFF'
  FROM user_tables
 WHERE table_name <> 'MIG_CHK' AND dropped = 'NO'
 ORDER BY table_name;
SPOOL OFF

-- gen_load.sql: 테이블마다 psql \copy 명령 생성
SPOOL /home/oracle/mig/pg/pg_02_load.sql
SELECT '\copy ' || LOWER(table_name) ||
       ' FROM ''/home/oracle/mig/pg/data/' || LOWER(table_name) || '.csv'' WITH (FORMAT csv)'
  FROM user_tables
 WHERE table_name <> 'MIG_CHK' AND dropped = 'NO'
 ORDER BY table_name;
SPOOL OFF
EXIT

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
SET MARKUP CSV ON QUOTE ON
SET HEADING OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET NUMWIDTH 38
SET LINESIZE 32767
SET TRIMSPOOL ON
@/home/oracle/mig/pg/ora_export.sql
EXIT

sqlplus -s <USERNAME>/<PASSWORD> @run_export.sql
ls -l data/
head data/orders.csv
wc -l data/*.csv
psql -h localhost -U <USERNAME> -d <TARGET_DB_NAME> -v ON_ERROR_STOP=1 -f pg_02_load.sql

@/home/oracle/mig/pg/set_gen.sql
SPOOL /home/oracle/mig/pg/pg_03_constraints.sql
SELECT ddl FROM (
  SELECT 1 AS ord, c.table_name AS tn,
         'ALTER TABLE ' || LOWER(c.table_name) || ' ADD CONSTRAINT ' || LOWER(c.constraint_name) ||
         DECODE(c.constraint_type, 'P', ' PRIMARY KEY (', ' UNIQUE (') ||
         LISTAGG(LOWER(cc.column_name), ', ') WITHIN GROUP (ORDER BY cc.position) || ');' AS ddl
    FROM user_constraints c
    JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
   WHERE c.constraint_type IN ('P', 'U') AND c.table_name <> 'MIG_CHK'
   GROUP BY c.table_name, c.constraint_name, c.constraint_type
  UNION ALL
  SELECT 2, c.table_name,
         'ALTER TABLE ' || LOWER(c.table_name) || ' ADD CONSTRAINT ' || LOWER(c.constraint_name) ||
         ' FOREIGN KEY (' || LISTAGG(LOWER(cc.column_name), ', ') WITHIN GROUP (ORDER BY cc.position) ||
         ') REFERENCES ' || LOWER(p.table_name) ||
         DECODE(c.delete_rule, 'CASCADE', ' ON DELETE CASCADE', '') || ';'
    FROM user_constraints c
    JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
    JOIN user_constraints  p  ON p.constraint_name  = c.r_constraint_name
   WHERE c.constraint_type = 'R'
   GROUP BY c.table_name, c.constraint_name, p.table_name, c.delete_rule
  UNION ALL
  SELECT 3, i.table_name,
         'CREATE ' || DECODE(i.uniqueness, 'UNIQUE', 'UNIQUE ', '') || 'INDEX ' || LOWER(i.index_name) ||
         ' ON ' || LOWER(i.table_name) || ' (' ||
         LISTAGG(LOWER(ic.column_name), ', ') WITHIN GROUP (ORDER BY ic.column_position) || ');'
    FROM user_indexes i
    JOIN user_ind_columns ic ON ic.index_name = i.index_name
   WHERE i.index_type = 'NORMAL' AND i.table_name <> 'MIG_CHK'
     AND i.index_name NOT IN (SELECT index_name FROM user_constraints WHERE index_name IS NOT NULL)
   GROUP BY i.table_name, i.index_name, i.uniqueness
)
ORDER BY ord, tn;
SPOOL OFF

SPOOL /home/oracle/mig/pg/pg_04_sequences.sql
SELECT 'DROP SEQUENCE IF EXISTS ' || LOWER(sequence_name) || ';' || CHR(10) ||
       'CREATE SEQUENCE ' || LOWER(sequence_name) ||
       ' START WITH ' || last_number || ' INCREMENT BY ' || increment_by ||
       DECODE(cycle_flag, 'Y', ' CYCLE', '') || ';'
  FROM user_sequences ORDER BY sequence_name;
SPOOL OFF
EXIT

psql -h localhost -U <USERNAME> -d <TARGET_DB_NAME> -v ON_ERROR_STOP=1 -f pg_03_constraints.sql
psql -h localhost -U <USERNAME> -d <TARGET_DB_NAME> -c '\d orders'
psql -h localhost -U <USERNAME> -d <TARGET_DB_NAME> -v ON_ERROR_STOP=1 -f pg_04_sequences.sql

SELECT last_value FROM ord_seq;
SELECT MAX(order_id) FROM orders;
SELECT setval('ord_seq', (SELECT MAX(order_id)::BIGINT FROM orders));

@/home/oracle/mig/pg/set_gen.sql
SPOOL /home/oracle/mig/pg/both_count.sql
SELECT LISTAGG('SELECT ''' || LOWER(table_name) || ''' AS table_name, COUNT(*) AS cnt FROM ' || LOWER(table_name),
               CHR(10) || 'UNION ALL' || CHR(10)) WITHIN GROUP (ORDER BY table_name)
       || CHR(10) || 'ORDER BY 1;'
  FROM user_tables
 WHERE table_name <> 'MIG_CHK' AND dropped = 'NO';
SPOOL OFF
EXIT

echo exit | sqlplus -s <USERNAME>/<PASSWORD> @both_count.sql
psql -h localhost -U <USERNAME> -d <TARGET_DB_NAME> -f both_count.sql

SELECT COUNT(*), SUM(amount), MIN(reg_date), MAX(reg_date) FROM orders;
SELECT SUM(sal) FROM emp_m;
SELECT order_id, cust_name FROM orders ORDER BY order_id;

ALTER TABLE orders ALTER COLUMN order_id SET DEFAULT nextval('ord_seq');
ALTER TABLE orders ALTER COLUMN reg_date SET DEFAULT LOCALTIMESTAMP(0);

BEGIN;
INSERT INTO orders (cust_name, amount) VALUES ('<NAME>', 55000) RETURNING *;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM order_hist;
ROLLBACK;