0.目标

通过Oracle数据泵工具导入导出数据

测试版本 Oracle Release 11.2.0.4.0 - 64bit Production
1 从原始库通过expdp 导出数据库指定对象或一个用户schema 到文件dmp中
2 通过impdp将数据文件导入到目标库中

1. 创建导出文件夹目录

1 2	SQL>create directory expdir as 'D:\myjava\olm\20170913\approbe0904'; SQL>grant read,write on directory expdir to public;

2. 查询已经有的directory

查看当前数据库导入导出路径

1	select * from dba_directories;

3. 导出dmp文件

注意
users是本地的表空间，请用实际的表空间替换users

操作系统命令：

1	expdp "'/ as sysdba'" directory=expdir dumpfile=si0915.dmp logfile=si0915_exp.log schemas=appuser

4.目标数据库导入全量表方法如下：

（1）删除approbe用户

1	drop user approbe cascade;

（2）导入新的全量数据

注意—users是本地的表空间，请用实际的表空间替换users
将appuser用户的数据导入到approbe用户下

操作系统命令：

1	impdp "'/ as sysdba'" directory=expdir dumpfile=si0915.dmp logfile=si0915_imp.log remap_schema=appuser:approbe

5.可能遇到的问题

5.1 问题1 ：Object type TYPE failed to create with error

5.1.1 错误信息

TYPE:
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "APPROBEBAK"."XXX_TAB_XXX_DDL"   OID '253439611478D21AE050A8C0D240D7B3' as object (
id number,
  PROJECT_ID number,
  module_id number,
  TABLE_NAME VARCHAR2(30),
  status varchar2(20),
  action varchar2(20),
  ddl_part_flag number,
  ddl_str varchar2(32767)
  )

ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "APPROBEBAK"."XXX_TAB_XXX_RM"   OID '253439611487D21AE050A8C0D240D7B3' as object
(
  owner          VARCHAR2(30),
  table_name     VARCHAR2(30),
  column_name    VARCHAR2(30),
  data_type      VARCHAR2(106),
  data_length    NUMBER,
  data_precision NUMBER,
  data_scale     NUMBER,
  nullable       VARCHAR2(1),
  column_id      NUMBER,
  default_length NUMBER,
  data_defau

5.1.2 错误原因

这个错误是因为同一个OID的对象已经在数据库中存在了，所以导致冲突无法创建，通过impdp help=y命令查看帮助，发现有参数如下：

1	123 TRANSFORM Metadata transform to apply to applicable objects. Valid transform keywords: SEGMENT_ATTRIBUTES, STORAGE OID, and PCTSPACE.

查看联机文档发现关于OID这个参数的解释如下：

1 OID - If the value is specified as n, the assignment of the exported OID during the creation of object tables and types is inhibited. Instead, a new OID is assigned. This can be useful for cloning schemas, but does not affect referenced objects. The default value is y.

意思就是说如果这个OID参数设置为“N”，那么导出的对象的OID在object table和type被创建时会被抑制，它会被分配一个新的OID，这个参数可以被用来clone schema，但是对被引用的对象是无效的。这个参数默认值为“Y”
于是只要将参数transform的OID显示的设置为N即可解决，如下所示：

http://www.ask600.com/ora-39083-object-type-type-failed-to-create-with-error.html

5.1.3 解决办法

1	impdp "'/ as sysdba'" directory=approbebak dumpfile=APPROBE_V2.4.DMP logfile=APPROBE_V2.4_imp.log remap_schema=approbe:approbebak transform=oid:n

5.2 问题2：违反唯一性约束 JOB_ID重复

5.2.1 错误日志

ORA-39083: Object type JOB failed to create with error:
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
Failing sql is:
 BEGIN SYS.XXXX.SUBMIT( JOB=> 401, LUSER=> 'XXX', PUSER=> 'XXX', CUSER=> 'XXX', NEXT_DATE=> TO_DATE('2014-11-13 22:03:45', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'SYSDATE + 120/1440  ', BROKEN=>  FALSE, WHAT=> 'dbms_refresh.refresh(''"XXX"."XXX"'');', NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMER
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" completed with 4 error(s) at Fri Nov 14 09:00:27 2014 elapsed 0 00:30:37

5.2.2 解决办法

（1）导出dmp文件中的ddl sql语句

1	impdp "'/ as sysdba'" directory=approbebak dumpfile=APPROBE_V2.4.DMP logfile=APPROBE_V2.4_imp.log remap_schema=approbe:approbebak transform=oid:n SQLFILE=impdp.sql

（2）在SQLFILE=impdp.sql找到相关JOB id的创建语句

-- new object type path: SCHEMA_EXPORT/JOB
 BEGIN SYS.DBMS_IJOB.SUBMIT(
      JOB=> 401,
      LUSER=> 'APPROBEBAK',
      PUSER=> 'APPROBEBAK',
      CUSER=> 'APPROBEBAK',
      NEXT_DATE=> TO_DATE('2017-12-15 09:08:27', 'YYYY-MM-DD:HH24:MI:SS'),
      INTERVAL=> 'sysdate+1',
      BROKEN=>  FALSE,
      WHAT=> 'pkg_XXXXX;',
      NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_DATE_FORMAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY''',
      ENV=> '0102000200000000');
      END;
 /

 BEGIN SYS.DBMS_IJOB.SUBMIT(
      JOB=> 402,
      LUSER=> 'APPROBEBAK',
      PUSER=> 'APPROBEBAK',
      CUSER=> 'APPROBEBAK',
      NEXT_DATE=> TO_DATE('2017-12-15 09:08:27', 'YYYY-MM-DD:HH24:MI:SS'),
      INTERVAL=> 'sysdate+1',
      BROKEN=>  FALSE,
      WHAT=> 'pkg_XXXXX.222_XXXXX;',
      NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_DATE_FORMAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY''',
      ENV=> '0102000200000000');
      END;
 /

（3）查到最大JOB_ID修改语句

1	select max (job) from dba_jobs;

（4）调整报错SQL语句为最大JOB_ID ，本例为403 404，手工创建如下语句 SYStem用户执行

-- new object type path: SCHEMA_EXPORT/JOB
 BEGIN SYS.DBMS_IJOB.SUBMIT(
      JOB=> 403,
      LUSER=> 'APPROBEBAK',
      PUSER=> 'APPROBEBAK',
      CUSER=> 'APPROBEBAK',
      NEXT_DATE=> TO_DATE('2017-12-15 09:08:27', 'YYYY-MM-DD:HH24:MI:SS'),
      INTERVAL=> 'sysdate+1',
      BROKEN=>  FALSE,
      WHAT=> 'pkg_XXXXX;',
      NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_DATE_FORMAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY''',
      ENV=> '0102000200000000');
      END;
 /

 BEGIN SYS.DBMS_IJOB.SUBMIT(
      JOB=> 404,
      LUSER=> 'APPROBEBAK',
      PUSER=> 'APPROBEBAK',
      CUSER=> 'APPROBEBAK',
      NEXT_DATE=> TO_DATE('2017-12-15 09:08:27', 'YYYY-MM-DD:HH24:MI:SS'),
      INTERVAL=> 'sysdate+1',
      BROKEN=>  FALSE,
      WHAT=> 'pkg_XXXXX.222_XXXXX;',
      NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_DATE_FORMAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY''',
      ENV=> '0102000200000000');
      END;
 /

（5）查看最后结果是否插入成功

SQL> select job,SCHEMA_USER,what from dba_jobs;

       JOB SCHEMA_USER
---------- ------------------------------
WHAT
--------------------------------------------------------------------------------
       403 APPROBEBAK
pkg_XXXXX.222_XXXXX;

       404 APPROBEBAK
pkg_XXXXX;

       402 APPROBE
pkg_XXXXX.222_XXXXX;


       JOB SCHEMA_USER
---------- ------------------------------
WHAT
--------------------------------------------------------------------------------
       401 APPROBE
pkg_XXXXX;


SQL>

6.欢迎关注米宝窝，持续更新中，谢谢！

米宝窝 https://rocklei123.github.io/