Oracle 不同环境下导入解决方案

来源：互联网发布：c 图片相似度算法编辑：程序博客网时间：2024/06/14 04:17

最近由于开发需要，要将现生产环境的数据库全部同步到开发环境中（包括数据、表结构、存储过程、方法……）。但是在导入的过程中发现了以下问题：

数据库服务端编码不一致，导入之后数据为乱码。
表中存在Blob和Clob类型的字段，在导入过程中出现了表不能导入或者导入后没有数据的情况。

由于数据源于生产库且不能随意访问（更不要说是上去操作了），现所有生产库数据都是通过已有脚本在晚上定时进行远程备份。查看后发现dmp文件是直接exp导出，没有使用数据泵。因此要导入的时候做一些特殊处理才能完全导入。

1 在开发库中用dba账号登陆，将当前账号下的所有表的column的comments文件和table的comments导出到excel。

导出column comments语句：

select 'comment on column '||table_name||'.'||column_name||' is '''||comments||''';' from dba_col_comments t where t.owner = 'xxx';

导出table comments语句：

select 'comment on table '||table_name||'  is '''||comments||''';' from dba_tab_comments t where t.owner = 'xxx';

查询后通过PLSQL的“Copy to Excel…”导出到Excel

这里写图片描述

2 将dmp拉到需要导入的环境，然后导出当前dmp的结构信息。

imp <xe_username>/<password>@XE file=<filename.dmp> indexfile=index.sql full=y

通过上面的语句会将dmp中的结构信息导出成一个sql文件，里面的内容大致如下：
这里写图片描述

3 用plsql打开这个结构的sql文件，执行5步替换操作：

找到: ‘REM ’ 替换: ”

找到: ‘”原来的表空间”’ 替换: ‘”目标表空间”’

找到: ‘…’ 替换: ‘REM …’

找到: ‘CONNECT’ 替换: ‘REM CONNECT’

找到: ‘REM ’ 替换: ‘–’

做完5步操作之后执行该脚本，则数据库中已经生成好表的结构了。

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 THEN          FOR E IN R LOOP              BEGIN                   ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;                   EXECUTE IMMEDIATE (ST);              EXCEPTION WHEN OTHERS THEN                   NULL;                   DBMS_OUTPUT.PUT_LINE(ST);              END;          END LOOP;  END IF;  --执行主键约束的关闭  IF PK THEN     -- 先关闭外键     FOR E IN R LOOP         BEGIN              ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;              EXECUTE IMMEDIATE (ST);         EXCEPTION WHEN OTHERS THEN                   NULL;                   DBMS_OUTPUT.PUT_LINE(ST);         END;     END LOOP;     --再关闭主键     FOR E IN P LOOP         BEGIN              ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;              EXECUTE IMMEDIATE (ST);          EXCEPTION WHEN OTHERS THEN                     NULL;                     DBMS_OUTPUT.PUT_LINE(ST);           END;      END LOOP;  END IF;  --执行唯一约束的关闭  IF UK THEN     FOR E IN U LOOP           BEGIN              ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;              EXECUTE IMMEDIATE (ST);           EXCEPTION WHEN OTHERS THEN                     NULL;                     DBMS_OUTPUT.PUT_LINE(ST);           END;      END LOOP;  END IF;--若是启用约束的时候  ELSIF UPPER(OPERATION) IN ('ENABLE') THEN  --先启用主键  IF PK THEN     FOR E IN P LOOP         BEGIN              ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;              EXECUTE IMMEDIATE (ST);        EXCEPTION WHEN OTHERS THEN                     NULL;                     DBMS_OUTPUT.PUT_LINE(ST);         END;      END LOOP;  END IF;  --启用外键  IF FK THEN  --先主键启动     FOR E IN P LOOP         BEGIN              ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;              EXECUTE IMMEDIATE (ST);        EXCEPTION WHEN OTHERS THEN                     NULL;                     DBMS_OUTPUT.PUT_LINE(ST);         END;      END LOOP;    --在外键启动    FOR E IN R LOOP         BEGIN             ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;             EXECUTE IMMEDIATE (ST);        EXCEPTION WHEN OTHERS THEN                     NULL;                     DBMS_OUTPUT.PUT_LINE(ST);         END;      END LOOP;  END IF;  --启用唯一约束  IF UK THEN        FOR E IN U LOOP            BEGIN                ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;                EXECUTE IMMEDIATE (ST);            EXCEPTION WHEN OTHERS THEN                         NULL;                         DBMS_OUTPUT.PUT_LINE(ST);             END;      END LOOP;  END IF;ELSE  DBMS_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窗口下执行即可

0 0