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的授权语句。
解决
- 确认丢失这些ROLE不会影响导入数据的目的,可以选择忽略。
- 从源端获取ROLE的DDL语句,目标端先创建ROLE,再执行IMPDP导入。
- 将缺少的模式和角色导入目标数据库,然后重新导入授权:
-- 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
- 可以通过确保导入开始之前目标数据库中也存在模式和角色来避免错误
-- 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
- 通过在导出或导入数据泵作业期间排除所有对象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 schema using the following steps:
- Export the ORS schema without views.
- Export views alone from one environment.
- Import the exported schema without views.
- Import the exported views and manually copy the problematic views from source environment and run it on target database.
ORA-39083 ORA-02304 Object type TYPE failed to create
问题描述
我用expdp,impdp复制一个shema,在impdp导入的时候报错
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "GENIDCDEV2"."ARRAY_TYPE2" OID '0367306C82464BF1E0534E092D0A4AB5' is array(48) of varchar2(255)
查看官方文档的impdp章节,搜索OID,发现了如下有用的内容
查看impdp help=y 可以使用一个参数:transform
TRANSFORM
Enables you to alter object creation DDL for objects being imported.
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.
这个参数默认是Y,如果改成N,那么导入的时候会把各个对象重新赋予新的OID。
transform=oid:n
这样,再用impdp导入的时候就不会出现
ORA-39083,ORA-02304
注意:exp/imp不支持oid转换,又是一个使用数据泵的优势。
处理
两种方法可以解决问题
1、用下面impdp语句重新导入
impdp system/'*****' directory=dir_lisx REMAP_SCHEMA=WMSUAT:GENIDCDEV2 dumpfile=wmsuat20150304.dump include=TYPE transform=oid:n logfile=impdp_wmsuat20150304_2.log
Import: Release 11.2.0.3.0 - Production on Wed Mar 4 14:55:40 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.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=dir_lisx REMAP_SCHEMA=WMSUAT:GENIDCDEV2 dumpfile=wmsuat20150304.dump include=TYPE transform=oid:n logfile=impdp_wmsuat20150304_2.log
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 14:55:45
2、手工重建刚才导入失败的type
-- 核查type的ddl语句
SELECT dbms_metadata.get_ddl('TYPE','ARRAY_TYPE2','WMSUAT') from dual ;
CREATE OR REPLACE TYPE "WMSUAT"."ARRAY_TYPE2" is array(48) of varchar2(255) ;
-- 记得重新编译一下新clone的schema
EXEC DBMS_UTILITY.compile_schema(schema => 'GENIDCDEV2');
mpdp全库导入时报错ORA-39083,ORA-23327,ORA-31693,ORA-31640等
最近做impdp数据泵全库导入的时候遇到了好多报错,现在记录下处理过程。
导入命令:
impdp \'/ as sysdba\' directory=EXPDP2020 dumpfile=xxxdb_%U.dump logfile=xxxdb_impdp.log PARALLEL=4 full=y table_exists_action=replace
报错如下:
.....
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
>>> Cannot set an SCN larger than the current SCN. If a Streams Capture configuration was imported then the Apply that processes the captured messages needs to be dropped and recreated. See My Oracle Support article number 1380295.1.
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39083: Object type TABLE:"ORDDATA"."ORDDCM_DOCS_TMP" failed to create with error:
ORA-01031: insufficient privileges
Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "ORDDATA"."ORDDCM_DOCS_TMP" ("DOC_ID" NUMBER(*,0) NOT NULL ENABLE, "DOC_NAME" VARCHAR2(100 CHAR) NOT NULL ENABLE, "DOC_TYPE_ID" NUMBER NOT NULL ENABLE, "DOC_CONTENT" "SYS"."XMLTYPE" NOT NULL ENABLE, "ORACLE_INSTALL" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, "CREATE_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE) ON COMMIT PRESERVE ROWS
ORA-39083: Object type TABLE:"ORDDATA"."ORDDCM_CT_PRED_OPRD_TMP" failed to create with error:
ORA-01031: insufficient privileges
Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "ORDDATA"."ORDDCM_CT_PRED_OPRD_TMP" ("PID" NUMBER(*,0), "POS" NUMBER(3,0), "OPERAND" "SYS"."XMLTYPE" ) ON COMMIT PRESERVE ROWS
ORA-39083: Object type TABLE:"ORDDATA"."ORDDCM_DOCS_WRK" failed to create with error:
ORA-01031: insufficient privileges
Failing sql is:
CREATE TABLE "ORDDATA"."ORDDCM_DOCS_WRK" ("DOC_ID" NUMBER(*,0), "DOC_NAME" VARCHAR2(100 CHAR) NOT NULL ENABLE, "DOC_TYPE_ID" NUMBER NOT NULL ENABLE, "DOC_CONTENT" "SYS"."XMLTYPE" NOT NULL ENABLE, "ORACLE_INSTALL" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, "CREATE_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 2
ORA-39083: Object type TABLE:"ORDDATA"."ORDDCM_MAPPING_DOCS_WRK" failed to create with error:
ORA-01031: insufficient privileges
Failing sql is:
CREATE TABLE "ORDDATA"."ORDDCM_MAPPING_DOCS_WRK" ("DOC_ID" NUMBER(*,0) NOT NULL ENABLE, "ROOT_TAG" VARCHAR2(128 CHAR) NOT NULL ENABLE, "METADATA_NS" VARCHAR2(700 CHAR), "MAPPED_ELEM" VARCHAR2(128 CHAR), "UNMAPPED_ELEM" VARCHAR2(128 CHAR), "XSLT" "SYS"."XMLTYPE" , "IS_COVERED" NUMBER(1,0) NOT NULL ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 M
ORA-39083: Object type TABLE:"ORDDATA"."ORDDCM_MAPPING_DOCS" failed to create with error:
ORA-01031: insufficient privileges
Failing sql is:
CREATE TABLE "ORDDATA"."ORDDCM_MAPPING_DOCS" ("DOC_ID" NUMBER(*,0) NOT NULL ENABLE, "ROOT_TAG" VARCHAR2(128 CHAR) NOT NULL ENABLE, "METADATA_NS" VARCHAR2(700 CHAR), "MAPPED_ELEM" VARCHAR2(128 CHAR), "UNMAPPED_ELEM" VARCHAR2(128 CHAR), "XSLT" "SYS"."XMLTYPE" , "IS_COVERED" NUMBER(1,0) NOT NULL ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
ORA-39083: Object type TABLE:"ORDDATA"."ORDDCM_CT_PRED_OPRD_WRK" failed to create with error:
ORA-01031: insufficient privileges
Failing sql is:
CREATE TABLE "ORDDATA"."ORDDCM_CT_PRED_OPRD_WRK" ("PID" NUMBER(*,0), "POS" NUMBER(3,0), "OPERAND" "SYS"."XMLTYPE" ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_
ORA-39083: Object type TABLE:"ORDDATA"."ORDDCM_MAPPING_DOCS_TMP" failed to create with error:
ORA-01031: insufficient privileges
Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "ORDDATA"."ORDDCM_MAPPING_DOCS_TMP" ("DOC_ID" NUMBER(*,0) NOT NULL ENABLE, "ROOT_TAG" VARCHAR2(128 CHAR) NOT NULL ENABLE, "METADATA_NS" VARCHAR2(700 CHAR), "MAPPED_ELEM" VARCHAR2(128 CHAR), "UNMAPPED_ELEM" VARCHAR2(128 CHAR), "XSLT" "SYS"."XMLTYPE" , "IS_COVERED" NUMBER(1,0) NOT NULL ENABLE) ON COMMIT PRESERVE ROWS
ORA-39083: Object type TABLE:"ORDDATA"."ORDDCM_DOCS" failed to create with error:
ORA-01031: insufficient privileges
Failing sql is:
CREATE TABLE "ORDDATA"."ORDDCM_DOCS" ("DOC_ID" NUMBER(*,0), "DOC_NAME" VARCHAR2(100 CHAR) NOT NULL ENABLE, "DOC_TYPE_ID" NUMBER NOT NULL ENABLE, "DOC_CONTENT" "SYS"."XMLTYPE" NOT NULL ENABLE, "ORACLE_INSTALL" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, "CREATE_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOM
ORA-39083: Object type TABLE:"ORDDATA"."ORDDCM_CT_PRED_OPRD" failed to create with error:
ORA-01031: insufficient privileges
Failing sql is:
CREATE TABLE "ORDDATA"."ORDDCM_CT_PRED_OPRD" ("PID" NUMBER(*,0), "POS" NUMBER(3,0), "OPERAND" "SYS"."XMLTYPE" ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
ORA-23327: imported deferred rpc data does not match GLOBAL NAME of importing db
Failing sql is:
BEGIN
SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('FINCDB','x86_64/Linux 2.4.xx');
END;
ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
ORA-23327: imported deferred rpc data does not match GLOBAL NAME of importing db
Failing sql is:
BEGIN
SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('FINCDB','x86_64/Linux 2.4.xx');
END;
ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
ORA-23327: imported deferred rpc data does not match GLOBAL NAME of importing db
Failing sql is:
BEGIN
SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('FINCDB','x86_64/Linux 2.4.xx');
END;
ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
ORA-23327: imported deferred rpc data does not match GLOBAL NAME of importing db
Failing sql is:
BEGIN
SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('FINCDB','x86_64/Linux 2.4.xx');
END;
ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
ORA-23327: imported deferred rpc data does not match GLOBAL NAME of importing db
Failing sql is:
BEGIN
SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('FINCDB','x86_64/Linux 2.4.xx');
END;
ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
ORA-23327: imported deferred rpc data does not match GLOBAL NAME of importing db
Failing sql is:
BEGIN
SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('FINCDB','x86_64/Linux 2.4.xx');
END;
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-31693: Table data object "SMART"."TD_CUST_CAPITAL_LOG_H":"P202009" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/home/db/oracle/backup/fincdb_20201124_04.dump" for read
ORA-19505: failed to identify file "/home/db/oracle/backup/fincdb_20201124_04.dump"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-31693: Table data object "SMART"."TD_CUST_CAPITAL_LOG_H":"P202007" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/home/db/oracle/backup/fincdb_20201124_02.dump" for read
ORA-19505: failed to identify file "/home/db/oracle/backup/fincdb_20201124_02.dump"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
错误处理:
1、Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Cannot set an SCN larger than the current SCN. If a Streams Capture configuration was imported then the Apply that processes the captured messages needs to be dropped and recreated. See My Oracle Support article number 1380295.1.
处理:添加参数STREAMS_CONFIGURATION=N,参考文档1380295.1
2、忽略,也可以手动执行失败的sql,可以执行成功。参考文档 DOC id 1909772.1
ORA-39083: Object type TABLE:"ORDDATA"."ORDDCM_DOCS_TMP" failed to create with error:
ORA-01031: insufficient privileges
3、global name不匹配
ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
ORA-23327: imported deferred rpc data does not match GLOBAL NAME of importing db
#目标端查询:
SQL> select * from global_name;
GLOBAL_NAME
-------------------------------------------------------------------------
XXPDB
alter database rename global_name to XXXCDB;
impdp
alter database rename global_name to XXPDB;
临时将global_name改为源端的global_name,导入完成后再改回去。 参考文档:DOC id 1568721.1
4、添加参数 clutter=n 参考文档 DOC id 14672321.1
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-31693: Table data object "SMART"."TD_CUST_CAPITAL_LOG_H":"P202009" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/home/db/oracle/backup/fincdb_20201124_04.dump" for read
ORA-19505: failed to identify file "/home/db/oracle/backup/fincdb_20201124_04.dump"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
参考资料
https://www.modb.pro/db/25673
https://blog.itpub.net/31546994/viewspace-2754896
报错 ORA-39095: 转储文件空间已耗尽: 无法分配 8192 字节
39095, 00000, "Dump file space has been exhausted: Unable to allocate %s bytes"
// *Cause: The Export job ran out of dump file space before the job was
// completed.
// *Action: Reattach to the job and add additional dump files to the job
// restarting the job.
[oracle@testosa:/home/oracle]$
官方解释
Because each active worker process or I/O server process writes exclusively to one file at a
time, an insufficient number of files can have adverse effects. Some of the worker processes will be idle while waiting for files, thereby degrading the overall performance of the job. More
importantly, if any member of a cooperating group of parallel I/O server processes cannot obtain a file for output, then the export operation will be stopped with an ORA-39095 error.
Both situations can be corrected by attaching to the job using the Data Pump Export utility, adding more files using the ADD_FILE command while in interactive mode, and in the case of a stopped job, restarting the job.
大概意思就是说oracle的安全机制使得不允许多个写进程同时写一个dump文件,如果此时你的parallel(并发)数值大于(实际导出数据量/fileszie)的数值,那么就会有一些进程闲置,他们只能等待写进程也相当于多个进程在抢占一个进程。 当一个io线程在不能写dumpfile而不断等待的情况下就会报ORA-39095。
修改备份表达式,参考如下
expdp user/passwd directory=backup_dir dumpfile=\$DMP_FILE logfile=\$LOG_FILE schemas=onepay parallel=4 compression=ALL
parallel 线程数调小即可(也可尝试写入多个dumpfile,parallel不变)
参考资料
https://www.cnblogs.com/halberd-lee/p/11052422.html