DATAPUMP导入报ORA-39083和ORA-01917，用户或角色不存在 报错 ORA-39095: 转储文件空间已耗尽: 无法分配 8192 字节

ORA-39083

[oracle@testos:/home/oracle]$ oerr ora 39083
39083, 00000, "Object type %s failed to create with error:\n%s\nFailing sql is:\n%s"
// *Cause:  Examine original error code to determine actual cause
// *Action: Original error code will contain more information
[oracle@testos:/home/oracle]$

DATAPUMP导入报ORA-39083和ORA-01917，用户或角色不存在

现象

使用DATAPUMP expdp按schema导出，impdp导入到其他数据库。该schema包含role，而role在目标数据库中不存在。
impdp导入期间，出现以下错误：

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'SCOTT_READONLY_ROLE' does not exist

原因

schema级别的expdp导出不会导出role。
导入时报错ORA-39083和ORA-1917是由于目标数据库中不存在所提到的角色。
我们可以看到 DBA_EXPORT_OBJECTS 中提到的包含和排除对象类型名称。在下面的OBJECT_PATH列中，“ROLE” 和 “ROLE_GRANT” 仅出现在 DATABASE_EXPORT 中。在 SCHEMA_EXPORT 中，只有 “ROLE_GRANT”。

SQL> select het_type, object_path from dba_export_objects where het_type='DATABASE_EXPORT' and object_path like '%ROLE%';

HET_TYPE                       OBJECT_PATH
------------------------------ ----------------------------------------------------------------------
DATABASE_EXPORT                DATABASE_EXPORT/DVPS_POLICY/DVPS_COMMAND_RULE_GROUP/DVPS_ROLE
DATABASE_EXPORT                DATABASE_EXPORT/ROLE
DATABASE_EXPORT                DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
DATABASE_EXPORT                DATABASE_EXPORT/SCHEMA/ROLE_GRANT
DATABASE_EXPORT                DATABASE_EXPORT/XS_SECURITY/XS_ROLE
DATABASE_EXPORT                DATABASE_EXPORT/XS_SECURITY/XS_ROLESET
DATABASE_EXPORT                DATABASE_EXPORT/XS_SECURITY/XS_ROLE_GRANT
DATABASE_EXPORT                DEFAULT_ROLE
DATABASE_EXPORT                DVPS_COMMAND_RULE_GROUP/DVPS_ROLE
DATABASE_EXPORT                DVPS_POLICY/DVPS_COMMAND_RULE_GROUP/DVPS_ROLE
DATABASE_EXPORT                DVPS_ROLE
DATABASE_EXPORT                ROLE
DATABASE_EXPORT                ROLE_GRANT
DATABASE_EXPORT                SCHEMA/DEFAULT_ROLE
DATABASE_EXPORT                SCHEMA/ROLE_GRANT
DATABASE_EXPORT                XS_ROLE
DATABASE_EXPORT                XS_ROLESET
DATABASE_EXPORT                XS_ROLE_GRANT
DATABASE_EXPORT                XS_SECURITY/XS_ROLE
DATABASE_EXPORT                XS_SECURITY/XS_ROLESET
DATABASE_EXPORT                XS_SECURITY/XS_ROLE_GRANT

21 rows selected.

SQL> select het_type, object_path from dba_export_objects where het_type='SCHEMA_EXPORT' and object_path like '%ROLE%';

HET_TYPE                       OBJECT_PATH
------------------------------ ------------------------------------------------------------
SCHEMA_EXPORT                  DEFAULT_ROLE
SCHEMA_EXPORT                  ROLE_GRANT
SCHEMA_EXPORT                  SCHEMA_EXPORT/DEFAULT_ROLE
SCHEMA_EXPORT                  SCHEMA_EXPORT/ROLE_GRANT

可以看出，只有在完整数据库(full=y)导出选项中包含role的创建和授权。在SCHEMA级导出期间，它仅包括role的授权语句。

解决

1. 确认丢失这些ROLE不会影响导入数据的目的，可以选择忽略。
2. 从源端获取ROLE的DDL语句，目标端先创建ROLE，再执行IMPDP导入。
3. 将缺少的模式和角色导入目标数据库，然后重新导入授权：

-- on the source database, run another Export Data Pump job, and export the users and/or roles that were reported in the errors of the earlier import Data Pump job:
$ expdp system/<PASSWORD> directory=my_dir dumpfile=expdp_users.dmp logfile=expdp_users.log reuse_dumpfiles=y full=y include=user:\"in(\'TC2\', \'MY_ROLE\')\" include=role:\"in(\'TC2\', \'MY_ROLE\')\"

-- on the target database, import these users and roles:
$ impdp system/<PASSWORD> directory=my_dir dumpfile=expdp_users.dmp logfile=impdp_users.log full=y

-- on the target database, re-run an import of the grants:
$ impdp system/<PASSWORD> directory=my_dir dumpfile=expdp_tc.dmp logfile=impdp_tc.log schemas=tc include=grant

1. 可以通过确保导入开始之前目标数据库中也存在模式和角色来避免错误

-- on the source database, export the schema you want to move:
$ expdp system/<PASSWORD> directory=my_dir dumpfile=expdp_tc.dmp logfile=expdp_tc.log reuse_dumpfiles=y schemas=tc

-- on the source database, export the users and/or roles that have grants on objects owned by TC:
$ expdp system/<PASSWORD> directory=my_dir dumpfile=expdp_users.dmp logfile=expdp_users.log reuse_dumpfiles=y full=y include=user:\"in(\'TC2\', \'MY_ROLE\')\" include=role:\"in(\'TC2\', \'MY_ROLE\')\"

-- on the target database, import these users and roles:
$ impdp system/<PASSWORD> directory=my_dir dumpfile=expdp_users.dmp logfile=impdp_users.log full=y

-- on the target database, import the schema you want to move:
$ impdp system/<PASSWORD> directory=my_dir dumpfile=expdp_tc.dmp logfile=impdp_tc.log schemas=tc  

1. 通过在导出或导入数据泵作业期间排除所有对象GRANTS，可以避免错误

$ impdp system/<PASSWORD> directory=my_dir dumpfile=expdp_tc.dmp logfile=impdp_tc.log schemas=tc exclude=grant

请注意，这将排除导入schema的所有授权。

这个问题并不难，写这篇文章目的是学习一下dba_export_objects

参考文档

Schema Level Import Fails With ORA-39083, ORA-1917 (Doc ID 2047750.1)
IMPDP - ORA-39083 ORA-01917 (user or role does not exist) Errors On A Schema Or Table or Tablespace Level Import Data Pump Job (Doc ID 1916469.1)

ERROR: “ORS ORA-39083: Object type VIEW failed to create with error: ORA-00933: SQL command not properly ended” while importing exported ORS

Description

The ORS is exported (entire schema with data) from one environment by using expdp utility and imported to another environment with the help of impdp utility. While importing ORS into other environment using impdp utility, the following error occurs:

ORA-39083: Object type VIEW failed to create with error: 
ORA-00933: SQL command not properly ended 
Failing sql is: 
CREATE FORCE VIEW "RCM_ORS"."C_REPOS_TABLE_OBJECT_V" ("ROWID_TABLE_OBJECT", "ROWID_TABLE", "OBJECT_NAME", "OBJECT_DESC", "OBJECT_TYPE_CODE", "OBJECT_FUNCTION_TYPE_CODE", "PUBLIC_IND", "PARAMETER", "VALID_IND") AS select 'C' || trim(ts.rowid_table) rowid_table_object , ts.rowid_table rowid_table , 'CMXCL.START_CLEANSE' object_name , NVL(UNISTR('') || xf.Map_name, UNISTR('\0056\0049

This issue occurs because while exporting the ORS using expdp utility, views are not exported properly due to empty space on end of the line on some of the MDM views.

Solution

To resolve this issue, copy the