Oracle에서 MySQL로 이행

[client]
host=127.0.0.1
port=3306
user=<USERNAME>
password=<PASSWORD>
default-character-set=utf8mb4

[mysql]
database=<TARGET_DB_NAME>
local-infile=1

chmod 600 ~/.my.cnf
mysql -e "SELECT CURRENT_USER(), DATABASE(), @@character_set_client"
mysql -e "SHOW TABLES"

@/home/oracle/mig/my/set_gen.sql
SPOOL /home/oracle/mig/my/my_01_tables.sql
SELECT 'SET FOREIGN_KEY_CHECKS = 0;' FROM dual;
SELECT 'DROP TABLE IF EXISTS `' || LOWER(t.table_name) || '`;' || CHR(10) ||
       'CREATE TABLE `' || LOWER(t.table_name) || '` (' || CHR(10) ||
       LISTAGG('  `' || LOWER(c.column_name) || '` ' ||
         CASE
           WHEN c.data_type = 'NUMBER' AND c.data_precision IS NULL AND c.pk_col = 'Y' THEN 'BIGINT'
           WHEN c.data_type = 'NUMBER' AND c.data_precision IS NULL THEN 'DECIMAL(38,10)'
           WHEN c.data_type = 'NUMBER' AND c.data_scale = 0 AND c.data_precision <= 9  THEN 'INT'
           WHEN c.data_type = 'NUMBER' AND c.data_scale = 0 AND c.data_precision <= 18 THEN 'BIGINT'
           WHEN c.data_type = 'NUMBER' AND c.data_scale = 0 THEN 'DECIMAL(' || c.data_precision || ')'
           WHEN c.data_type = 'NUMBER'                     THEN 'DECIMAL(' || 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 'DATETIME'
           WHEN c.data_type LIKE 'TIMESTAMP%'              THEN 'DATETIME(6)'
           WHEN c.data_type IN ('FLOAT','BINARY_DOUBLE')   THEN 'DOUBLE'
           WHEN c.data_type = 'CLOB'                       THEN 'LONGTEXT'
           WHEN c.data_type = 'BLOB'                       THEN 'LONGBLOB'
           ELSE 'LONGTEXT /* 확인 필요: ' || c.data_type || ' */'
         END ||
         CASE WHEN c.nullable = 'N' THEN ' NOT NULL' END,
       ',' || CHR(10)) WITHIN GROUP (ORDER BY c.column_id)
       || CHR(10) || ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;' || CHR(10)
  FROM user_tables t
  JOIN (SELECT tc.table_name, tc.column_name, tc.column_id, tc.data_type,
               tc.data_precision, tc.data_scale, tc.char_length, tc.nullable,
               CASE WHEN EXISTS (SELECT 1
                                   FROM user_constraints k
                                   JOIN user_cons_columns kc ON kc.constraint_name = k.constraint_name
                                  WHERE k.constraint_type = 'P'
                                    AND kc.table_name  = tc.table_name
                                    AND kc.column_name = tc.column_name)
                    THEN 'Y' ELSE 'N' END AS pk_col
          FROM user_tab_columns tc) 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;
SELECT 'SET FOREIGN_KEY_CHECKS = 1;' FROM dual;
SPOOL OFF
EXIT

sqlplus -s <USERNAME>/<PASSWORD> @gen_ddl.sql
cat my_01_tables.sql
mysql < my_01_tables.sql
mysql -e "DESC orders"

@/home/oracle/mig/my/set_gen.sql
SPOOL /home/oracle/mig/my/my_02_load.sql
SELECT 'LOAD DATA LOCAL INFILE ''/home/oracle/mig/my/data/' || LOWER(t.table_name) || '.csv''' || CHR(10) ||
       '  INTO TABLE `' || LOWER(t.table_name) || '`' || CHR(10) ||
       '  CHARACTER SET utf8mb4' || CHR(10) ||
       q'[  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '']' || CHR(10) ||
       q'[  LINES TERMINATED BY '\n']' || CHR(10) ||
       '  (' || LISTAGG('@' || LOWER(c.column_name), ', ') WITHIN GROUP (ORDER BY c.column_id) || ')' || CHR(10) ||
       '  SET ' || LISTAGG('`' || LOWER(c.column_name) || '` = NULLIF(@' || LOWER(c.column_name) || ', '''')', ',' || CHR(10) || '      ')
                   WITHIN GROUP (ORDER BY c.column_id) || ';' || 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

SHOW GLOBAL VARIABLES LIKE 'local_infile';
SET PERSIST local_infile = ON;

SOURCE /home/oracle/mig/my/my_02_load.sql
SHOW WARNINGS;
SELECT * FROM orders ORDER BY order_id;

SELECT '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) || '` (' ||
       LISTAGG('`' || LOWER(pc.column_name) || '`', ', ') WITHIN GROUP (ORDER BY cc.position) || ')' ||
       DECODE(c.delete_rule, 'CASCADE', ' ON DELETE CASCADE', 'SET NULL', ' ON DELETE SET NULL', '') || ';'
  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
  JOIN user_cons_columns pc ON pc.constraint_name = p.constraint_name AND pc.position = cc.position
 WHERE c.constraint_type = 'R'
 GROUP BY c.table_name, c.constraint_name, p.table_name, c.delete_rule;

SELECT sequence_name, last_number, increment_by, cache_size FROM user_sequences;

SELECT d.name AS trigger_name, t.table_name, d.referenced_name AS sequence_name
  FROM user_dependencies d
  JOIN user_triggers t ON t.trigger_name = d.name
 WHERE d.referenced_type = 'SEQUENCE';

SELECT line, text FROM user_source WHERE name = 'TRG_ORDERS_BI' ORDER BY line;

SELECT MAX(order_id) FROM orders;
ALTER TABLE orders MODIFY order_id BIGINT NOT NULL AUTO_INCREMENT;
SHOW CREATE TABLE orders\G
SHOW INDEX FROM orders;
ALTER TABLE orders MODIFY reg_date DATETIME DEFAULT CURRENT_TIMESTAMP;

SELECT COUNT(*), SUM(amount), MIN(reg_date), MAX(reg_date) FROM orders;
SELECT order_id, cust_name, LENGTH(cust_name) AS chars, LENGTHB(cust_name) AS bytes
  FROM orders ORDER BY order_id;

SELECT COUNT(*), SUM(amount), MIN(reg_date), MAX(reg_date) FROM orders;
SELECT order_id, cust_name, CHAR_LENGTH(cust_name) AS chars, LENGTH(cust_name) AS bytes
  FROM orders ORDER BY order_id;

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

mysql -t < both_count.sql