impdp导入时报错 、impdp导入时很慢、expdp导出时很慢

impdp导入报错

导入过程中如果出现对象已存在、表或索引的统计信息导入出错的提示

现象

ORA-31684: Object type USER:"CS_INFO_DW" already exists
ORA-31684: Object type TYPE:"CS_PLATFORM"."STRING_SUM_OBJ" already exists
ORA-39083: Object type INDEX_STATISTICS

解决办法

导入完成后如果缺少统计信息可以在执行一遍统计收集，plsql命令窗口下，或sqlplus窗口下执行

exec dbms_stats.gather_database_stats(estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');

导入过程中提示ORA-39093,ORA-20000

[oracle@testos:/home/oracle]$oerr ora 39093
39093, 00000, "FLASHBACK automatically enabled to preserve database integrity."
// *Cause:  A Data Pump job was required to enable flashback support to
//          specific SCNs in order to preserve the consistency of a logical
//          standby or streams instantiation.
// *Action: None
[oracle@testos:/home/oracle]$
[oracle@testos:/home/oracle]$oerr ora 20000
20000, 00000, "%s"
// *Cause:  The stored procedure 'raise_application_error'
//          was called which causes this error to be generated.
// *Action: Correct the problem as described in the error message or contact
//          the application administrator or DBA for more information.
[oracle@testos:/home/oracle]$

解决办法：

忽略。报错原因，是建索引时未指定名称，采取默认编码，不同数据库编码值不一致导致。

导入过程中提示ORA-29913,ORA-08103

[oracle@testos:/home/oracle]$oerr ora 29913
29913, 00000, "error in executing %s callout"
// *Cause: The execution of the specified callout caused an error.
// *Action: Examine the error messages take appropriate action.
[oracle@testos:/home/oracle]$
[oracle@testos:/home/oracle]$oerr ora 08103
08103, 00000, "object no longer exists"
// *Cause:  The object has been deleted by another user since the operation
//          began, or a prior incomplete recovery restored the database to
//          a point in time during the deletion of the object.
// *Action: Delete the object if this is the result of an incomplete
//          recovery.
[oracle@testos:/home/oracle]$

解决办法：报错原因，由于导入时瞬间资源不足，重新上述命令执行处理

impdp导入时很慢

现象：当执行数据库导入impdp导出时，命令行一直无反应，或者等待几个小时也没有反应

常见原因：

• 数据磁盘极慢
• 数据库sga和pga设置过小
• redo日志较小

解决办法：

• 磁盘慢排查见第三部分，磁盘性能测试，如果磁盘问题，只有更换性能好的磁盘
• 调整sga和pga值
• 调整redo大小值

expdp导出时很慢

常见原因：

1. 数据磁盘极慢
2. 数据库steam pool满了

通常是问题2较多

#使用sys,system权限用户查询以下sql
SQL> select shrink_phase_knlasg from X$KNLASG;

SHRINK_PHASE_KNLASG
-------------------
                  0
#返回如果值为1，那么异常，为0正常。
#当值为1时，使用下面SQL处理
alter system set events 'immediate trace name mman_create_def_request level 6';