记一次impdp导入失败，失败指令为ORA-39002 ORA-39070 ORA-29283 ORA-06512 ORA-29283

「爱情、让人受尽委屈。」 2022-11-21 09:51 1292阅读 0赞

今天对生产机数据库使用impdf导入的时候，出现如下错误，错误指令为：  
“ORA-39002,ORA-39070,ORA-29283,ORA-06512,ORA-29283”。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70_pic_center]  
**特此记录解决方案如下：**

**第一步，排查目录是否存在，权限是否oracle：oinstall**。发现存在且权限正确。  
![在这里插入图片描述][20201031143405585.png_pic_center]  
**第二步，数据库中检查并确认是否授权**  
执行语句如下：

sqlplus / as sysdba;
    
    alter session set container=wmp;//如果需要修改容器，则执行
    select sys_context ('USERENV', 'CON_NAME') from dual;//查询容器更改是否生效
    
    create or replace directory DUMP_DIR as  '/backup/zqw/dump';//创建目录
    grant read,write on directory DUMP_DIR to enterprise,meta,wmsadmin,wmwhse1,wmwhse2,aacibus;//给用户授权

创建用户库并授权

create user meta PROFILE SCEUSER identified by WMmeta1 DEFAULT TABLESPACE SCE_COMMON TEMPORARY TABLESPACE SCE_COMMON_TMP;
    grant create any table to  meta;
    grant dba to meta;
    grant execute on DBMS_LOCK to meta;
    grant select on DBA_OBJECTS to meta;
    grant create any sequence to meta;
    grant read,write on directory DUMP_DIR to meta;

**第三步，发现重新执行导入语句还是报错，则需要更改’/backup/zqw/dump’目录权限，更改为777**

impdp meta/WMmeta1@WMPSRV DIRECTORY=DUMP_DIR DUMPFILE=meta.DMP REMAP_SCHEMA=meta:meta remap_tablespace=SCE_COMMON:SCE_COMMON  TRANSFORM=segment_attributes:n,OID:n EXCLUDE=STATISTICS logfile=imp_meta.log;

chmod -R 777 /backup/zqw/dump/  #更改目录权限777
    chown -R oracle:oinstall  /backup/zqw/dump/  #更改目录拥有者为oracle

最后，重新执行impdb命令发现导入成功。

**PS：如果还是无法成功导入，如果是多机子服务器，采用虚拟服务名，则在另外一台服务器上创建相同的目录即可！**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70_pic_center 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70_pic_center 2]  
如果出现ORA-31640错误：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70]  
则首先检查权限、目录是否正确，注意wmwhse30.DMP大小写，如果不规范，则导入不成功！**这里DMP一定要大写！**，改了之后就能正常导入了！  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/954e89a744764cfa9b0293fcf3073ff9.png
[20201031143405585.png_pic_center]: /images/20221120/4f69575cb7d74ddeaa191bb020649dc4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20201031144323381.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201031144343632.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201204174346272.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201204174511752.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ==,size_16,color_FFFFFF,t_70