这两天从oracle倒换数据到mysql涉及的表比较大(1.3亿),且oracle生产环境不允许本地连接,因此,倒换工具无法使用,只好通过导出成文本再导入mysql;
具体过程记录如下:
1.oracle以文本形式导出数据(1.3亿30min左右,文件大小22G),分隔符为','
/home/oracle/bin/sqluldr2_aix64_9206.bin sys/password sql=1.sql file=1.txt charset=utf8 batch=yes rows=5000000
--生成1.sql的sql如下:
SELECT CASE
WHEN COLUMN_ID = (SELECT MAX(COLUMN_ID)
FROM ALL_TAB_COLS
WHERE TABLE_NAME = UPPER('TABLE_NAME_1')
AND OWNER = 'TABLE_OWNER_1') THEN
SUBSTR(SQL_TEXT, 1, LENGTH(SQL_TEXT) - 5)||' from TABLE_OWNER_1.TABLE_NAME_1;'
ELSE
SQL_TEXT
END A
FROM (SELECT COLUMN_ID,
CASE
WHEN COLUMN_ID = 1 THEN
'select '
ELSE
'||'
END || CASE
WHEN DATA_TYPE = 'DATE' TH