本文已参与「新人创作礼」活动，一起开启掘金创作之路
文中使用的Oracle版本为11g。
最近由于开发需要，需要将现生产环境数据库全部同步到开发环境中（包括数据、表结构、存储过程、方法......）。但是在导入的过程中发现了以下问题：
- 数据库服务端编码不一致，导入之后数据为乱码。
- 表中存在Blob和Clob类型的字段，在导入过程中出现了表不能导入或者导入后没有数据的情况。
由于数据来源是生产库且不能随意访问，因此所有生产数据都将通过脚本晚上定时执行远程备份（脚本是之前同事留下的，离职后没有人维护又将这个烂摊子给到自己头上了）。
在查阅脚本后发现，导出的dmp文件是直接通过exp导出，没有使用到expdp（数据泵），因此在导入时需要做一些特殊处理才能完全导入，共7步下面将进行详细说明。
1 开发库中采用dba账号登录，将当前账号下的所有表描述及其字段描述导出到excel中
导出字段描述语句：
select 'comment on column '||table_name||'.'||column_name||' is '''||comments||''';' from dba_col_comments t where t.owner = 'xxx';
导出表描述语句：
select 'comment on table '||table_name||' is '''||comments||''';' from dba_tab_comments t where t.owner = 'xxx';
查询结果可以使用PL/SQL的“Copy to Excel...”导出到Excel，如下图：
2 将生产导出的dmp文件上传到导入环境服务器，然后导出当前dmp的结构信息
imp <xe_username>/<password>@XE file=<filename.dmp> indexfile=index.sql full=y
通过上面的语句会将dmp文件中的结构信息导出成一个sql文件，里面的内容大致如下：
3 用PL/SQL打开sql文件，执行以下5步替换操作
- 找到: 'REM ' 替换: ''
- 找到: '"原来的表空间"' 替换: '"目标表空间"'
- 找到: '...' 替换: 'REM ...'
- 找到: 'CONNECT' 替换: 'REM CONNECT'
- 找到: 'REM ' 替换: '--'
完成操作后执行该脚本，数据库就能够生成好表结构了。
4 执行存储过程干掉所有约束
exec MANAGE_USER_CONSTRAINTS('disable',true,true,true);
存储过程代码如下：
CREATE OR REPLACE PROCEDURE MANAGE_USER_CONSTRAINTS(OPERATION VARCHAR2, FK BOOLEAN DEFAULT TRUE, PK BOOLEAN DEFAULT TRUE, UK BOOLEAN DEFAULT TRUE) IS/**启动和关闭约束的存储过程脚本created by yuanzh 2015-10-22*/ST VARCHAR2(255);--获取外键关系CURSOR R IS SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R';--获取主键CURSOR P IS SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'P' AND INSTR(CONSTRAINT_NAME,'BIN') = 0;--获取唯一索引CURSOR U IS SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'U';BEGIN--如果参数中是disable，关闭约束的情况下IF UPPER(OPERATION) IN ('DROP', 'DISABLE') THEN--先执行外键约束的关闭IF FK THENFOR E IN R LOOPBEGINST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;EXECUTE IMMEDIATE (ST);EXCEPTION WHEN OTHERS THENNULL;DBMS_OUTPUT.PUT_LINE(ST);END;END LOOP;END IF;--执行主键约束的关闭IF PK THEN-- 先关闭外键FOR E IN R LOOPBEGINST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;EXECUTE IMMEDIATE (ST);EXCEPTION WHEN OTHERS THENNULL;DBMS_OUTPUT.PUT_LINE(ST);END;END LOOP;--再关闭主键FOR E IN P LOOPBEGINST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;EXECUTE IMMEDIATE (ST);EXCEPTION WHEN OTHERS THENNULL;DBMS_OUTPUT.PUT_LINE(ST);END;END LOOP;END IF;--执行唯一约束的关闭IF UK THENFOR E IN U LOOPBEGINST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;EXECUTE IMMEDIATE (ST);EXCEPTION WHEN OTHERS THENNULL;DBMS_OUTPUT.PUT_LINE(ST);END;END LOOP;END IF;--若是启用约束的时候 ELSIF UPPER(OPERATION) IN ('ENABLE') THEN--先启用主键IF PK THENFOR E IN P LOOPBEGINST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;EXECUTE IMMEDIATE (ST);EXCEPTION WHEN OTHERS THENNULL;DBMS_OUTPUT.PUT_LINE(ST);END;END LOOP;END IF;--启用外键IF FK THEN--先主键启动FOR E IN P LOOPBEGINST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;EXECUTE IMMEDIATE (ST);EXCEPTION WHEN OTHERS THENNULL;DBMS_OUTPUT.PUT_LINE(ST);END;END LOOP;--在外键启动FOR E IN R LOOPBEGINST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;EXECUTE IMMEDIATE (ST);EXCEPTION WHEN OTHERS THENNULL;DBMS_OUTPUT.PUT_LINE(ST);END;END LOOP;END IF;--启用唯一约束IF UK THENFOR E IN U LOOPBEGINST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;EXECUTE IMMEDIATE (ST);EXCEPTION WHEN OTHERS THENNULL;DBMS_OUTPUT.PUT_LINE(ST);END;END LOOP;END IF;ELSEDBMS_OUTPUT.PUT_LINE('THE FIRST PARAMETER OF THE PROCEDURE MUST BE DROP OR ENABLE OR DISABLE');END IF;END;
5 执行imp导入
imp xxx/abc@se file=xxx220151020_235000.dmp fromuser=fuser touser=tuser ignore=y grants=n constraints=n rows=y buffer=80000000
6 导入完成之后再重新执行刚才的存储过程开启约束
exec MANAGE_USER_CONSTRAINTS('enable',true,true,true);
7 返回第一点中数据提取的excel，将里面的脚本在Command窗口下执行即可
