一次ORA-39083,ORA-01917的问题解决过程
今天到导数据的时候遇到了一个问题，也不知道有没有其他小伙伴也遇到过。按照要求导出一个用户下指定表的部分数据，此时使用了expdp的tables参数指定要导出的表这里我导出的是BKG_MAINT用户下的RAIL_SIGHTING，导出命令如下：
expdp goldengate/goldengate DIRECTORY=expdp20161012 DUMPFILE=BKG_MAINT_RAIL_SIGHTING.dmp tables=BKG_MAINT.RAIL_SIGHTING COMPRESSION=ALL PARALLEL=2 cluster=n logfile=expdpBKG_MAINT_RAIL_SIGHTING.log
导出过程如下：
[oracle@lxppasora1 oradata]$
expdp goldengate/goldengate DIRECTORY=expdp20161012 DUMPFILE=BKG_MAINT_RAIL_SIGHTING.dmp tables=BKG_MAINT.RAIL_SIGHTING COMPRESSION=ALL PARALLEL=2 cluster=n logfile=expdpBKG_MAINT_RAIL_SIGHTING.log
Export: Release 11.2.0.4.0 - Production on Wed Oct 12 12:27:34 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "GOLDENGATE"."SYS_EXPORT_TABLE_01": goldengate/******** DIRECTORY=expdp20161012 DUMPFILE=BKG_MAINT_RAIL_SIGHTING.dmp tables=BKG_MAINT.RAIL_SIGHTING COMPRESSION=ALL PARALLEL=2 cluster=n logfile=expdpBKG_MAINT_RAIL_SIGHTING.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
. . exported "BKG_MAINT"."RAIL_SIGHTING" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "GOLDENGATE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for GOLDENGATE.SYS_EXPORT_TABLE_01 is:
/expdp20161012/BKG_MAINT_RAIL_SIGHTING.dmp
Job "GOLDENGATE"."SYS_EXPORT_TABLE_01" successfully completed at Wed Oct 12 12:28:15 2016 elapsed 0 00:00:35
注意：这里可以从导出信息中注意到红色部分，并没有导出schema信息。
结果导入时报错，报错信息如下：
[oracle@lxppasora1 oradata]$
impdp system/oracle DIRECTORY=expdp20161012 DUMPFILE=BKG_MAINT_RAIL_SIGHTING.dmp exclude=statistics PARALLEL=2 logfile=impdpBKG_MAINT_RAIL_SIGHTING.log
Import: Release 11.2.0.4.0 - Production on Thu Oct 13 10:02:02 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=expdp20161012 DUMPFILE=BKG_MAINT_RAIL_SIGHTING.dmp exclude=statistics PARALLEL=2 logfile=impdpBKG_MAINT_RAIL_SIGHTING.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" failed to create with error:
ORA-01918: user 'BKG_MAINT' does not exist
Failing sql is:
CREATE TABLE "BKG_MAINT"."RAIL_SIGHTING" ("RAIL_SIGHTING_UUID" NUMBER(20,0) NOT NULL ENABLE, "TRCKG_GRP_UUID" NUMBER(20,0) NOT NULL ENABLE, "TRCKG_GRP_REF_CDE" VARCHAR2(12 BYTE) NOT NULL ENABLE, "PACKAGE_UUID" NUMBER(20,0) NOT NULL ENABLE, "INTERNAL_SHMT_NUM" VARCHAR2(7 BYTE) NOT NULL ENABLE, "SEQ_NUM" NUMBER(4,0), "RAIL_MV_STAT" VARCHAR2(1 BYTE), "RAIL_CARRIER" VA
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39112: Dependent object type OBJECT_GRANT:"BKG_MAINT" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"BKG_MAINT" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"BKG_MAINT" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"BKG_MAINT" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"BKG_MAINT" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"BKG_MAINT" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"BKG_MAINT" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"BKG_MAINT" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"BKG_MAINT" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"BKG_MAINT" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"BKG_MAINT" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"BKG_MAINT" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"BKG_MAINT" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"BKG_MAINT"."RAIL_SIGHTINGI2" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
ORA-39112: Dependent object type INDEX:"BKG_MAINT"."RAIL_SIGHTINGI1" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"BKG_MAINT"."GGS_102503" skipped, base object type TABLE:"BKG_MAINT"."RAIL_SIGHTING" creation failed
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 17 error(s) at Thu Oct 13 10:02:03 2016 elapsed 0 00:00:01
分析：由于在导出的时候没有导出user信息，所以在导入的时候就不会自动创建相应的user,一般我们按schema导出用户的时候会直接导出user信息，但是我使用上面的expdp导出表的时候，相当于直接导出了table中的数据，而不包括user信息。所以导入的时候就报错。
从导出的输出信息可以看出： object type TABLE_EXPORT,只导出了表中的数据。
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
此时需要导入用户信息：
1.先导出用户信息
[oracle@lxppasora1 oradata]$ expdp goldengate/goldengate DIRECTORY=EXDP20160727 DUMPFILE=user.dmp SCHEMAS=BKG_MAINT,BL_MAINT,CPF_MAINT,EDI_MAINT,EMP_MAINT,EQP_MAINT,GSP_MAINT,HOUSEBL_MAINT,ITS_MAINT LOGFILE=user.log include=USER,SYSTEM_GRANT,ROLE_GRANT,DEFAULT_ROLE,PROCACT_SCHEMA cluster=n
2.在目标端导入用户信息：
impdp system/oracle DIRECTORY=expdp20161012 DUMPFILE=user.dmp LOGFILE=imp_user.log
导入之后就能正常导入了。
使用同样的导入命令
[oracle@lxppasora1 oradata]$ impdp system/oracle DIRECTORY=expdp20161012 DUMPFILE=BKG_MAINT_RAIL_SIGHTING.dmp exclude=statistics PARALLEL=2 logfile=impdpBKG_MAINT_RAIL_SIGHTING.log
Import: Release 11.2.0.4.0 - Production on Thu Oct 13 15:44:24 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_05" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_05": system/******** DIRECTORY=expdp20161012 DUMPFILE=BKG_MAINT_RAIL_SIGHTING.dmp exclude=statistics PARALLEL=2 logfile=impdpBKG_MAINT_RAIL_SIGHTING.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "BKG_MAINT"."RAIL_SIGHTING" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OOGUEST' does not exist
Failing sql is:
GRANT SELECT ON "BKG_MAINT"."RAIL_SIGHTING" TO "OOGUEST"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'ODSSELECT' does not exist
Failing sql is:
GRANT SELECT ON "BKG_MAINT"."RAIL_SIGHTING" TO "ODSSELECT"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'STAG_MAINT' does not exist
Failing sql is:
GRANT FLASHBACK ON "BKG_MAINT"."RAIL_SIGHTING" TO "STAG_MAINT"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'STAG_MAINT' does not exist
Failing sql is:
GRANT DEBUG ON "BKG_MAINT"."RAIL_SIGHTING" TO "STAG_MAINT"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'STAG_MAINT' does not exist
Failing sql is:
GRANT QUERY REWRITE ON "BKG_MAINT"."RAIL_SIGHTING" TO "STAG_MAINT"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'STAG_MAINT' does not exist
Failing sql is:
GRANT ON COMMIT REFRESH ON "BKG_MAINT"."RAIL_SIGHTING" TO "STAG_MAINT"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'STAG_MAINT' does not exist
Failing sql is:
GRANT REFERENCES ON "BKG_MAINT"."RAIL_SIGHTING" TO "STAG_MAINT"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'STAG_MAINT' does not exist
Failing sql is:
GRANT UPDATE ON "BKG_MAINT"."RAIL_SIGHTING" TO "STAG_MAINT"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'STAG_MAINT' does not exist
Failing sql is:
GRANT SELECT ON "BKG_MAINT"."RAIL_SIGHTING" TO "STAG_MAINT"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'STAG_MAINT' does not exist
Failing sql is:
GRANT INSERT ON "BKG_MAINT"."RAIL_SIGHTING" TO "STAG_MAINT"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'STAG_MAINT' does not exist
Failing sql is:
GRANT INDEX ON "BKG_MAINT"."RAIL_SIGHTING" TO "STAG_MAINT"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'STAG_MAINT' does not exist
Failing sql is:
GRANT DELETE ON "BKG_MAINT"."RAIL_SIGHTING" TO "STAG_MAINT"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'STAG_MAINT' does not exist
Failing sql is:
GRANT ALTER ON "BKG_MAINT"."RAIL_SIGHTING" TO "STAG_MAINT"
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_05" completed with 13 error(s) at Thu Oct 13 15:44:26 2016 elapsed 0 00:00:01
此时可以看到impdp已经completed with 13 error(s) ，其中13个error是因为我只导出了部分用户，所以在导入授权信息的时候就会报相应的对象不存在，所以下面的报错可以忽略：
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'STAG_MAINT' does not exist
