【Troubleshooting】ORA-31626 job does not exist and ORA-31633 unable to create master table when executing EXPDP




  • 数据库平台:Linux 5.8
    数据库版本:oracle 11.2.0.1
    【问题描述】
    使用脚本进行expdp导出,报错如下:

    ORA-31626: job does NOT exist
    ORA-31633: unable TO CREATE master TABLE "ULTRANMS.EXPDP_ULTRANMS"
    ORA-06512: AT "SYS.DBMS_SYS_ERROR", line 95
    ORA-06512: AT "SYS.KUPV$FT", line 1020
    ORA-00955: name IS already used BY an existing object

    截图如下:
    1.[ID20130627.1]

    【处理方法】
    ORA-31626,ORA-31633的报错中明确指出了问题所在。首先是job不存在,其次无法创建expdp_ultranms表,最后说明对象已经存在。
    查看执行的脚本内容:

    [oracle@IT-DB ]cat NMSDB_backup.sh
    LANG=C
    export LANG
    ORACLE_SID=citinms
    export ORACLE_SID
    NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
    export NLS_LANG
    umask 022

    DATE=`date +%Y%m%d`

    #find /oradatabak -name "nmsdbbak_*.dmp" -type f -mtime +1 -exec rm {} \;
    #find /oradatabak -name "nmsdbbak_*.log" -type f -mtime +1 -exec rm {} \;
    #find /oradatabak -name "nmsdbbak_*.tar.gz" -type f -mtime +7 -exec rm {} \;

    sqlplus /nolog << EOF
    conn /as sysdba
    create or replace directory backup_expdp as '/oradata';
    grant read,write on directory backup_expdp to public;
    quit
    EOF


    if [ -d "/oradata" ]; then
    expdp ultranms/ULTRANMS directory=backup_expdp dumpfile=nmsdbbak_$DATE.dmp schemas=ultranms exclude=statistics job_name=expdp_ultranms logfile=nmsdbbak_$DATE.log
    fi

    #gzip -9 /oradata/nmsdbbak_$DATE.dmp
    #cd /oradata/
    #tar -zcvf nmsdbbak_$DATE.tar.gz nmsdbbak_$DATE.dmp

    #cd /oradatabak/
    #exp ultranms/ULTRANMS file=nmsdbbak_$DATE.dmp log=nmsdbbak_$DATE.log full=y direct=y
    #tar -zcvf nmsdbbak_$DATE.tar.gz nmsdbbak_$DATE.dmp
    #cd /oradatabak/
    #scp nmsdbbak_$DATE.dmp root@10.22.65.147:/back/oradatabak/
    #scp nmsdbbak_$DATE.log root@10.22.65.147:/back/oradatabak/
    #scp nmsdbbak_$DATE.tar.gz root@10.22.65.147:/back/oradatabak/
    #scp nmsdbbak_$DATE.dmp root@10.22.65.147:/back/oradatabak/
    #scp nmsdbbak_$DATE.log root@10.22.65.147:/back/oradatabak/
    #scp ardbbak_$DATE.dmp root@10.22.65.147:/back/oradatabak/
    #scp ardbbak_$DATE.log root@10.22.65.147:/back/oradatabak/

    脚本中expdp命令,果然使用了job,删除job_name=expdp_ultranms,导出正常了。
    2.[ID20130627.1]
    另外,如果删除expdp_ultranms表,也应该可以解决这个问题。但由于删除操作还是存在风险,因此并没有尝试

    Speak Your Mind

    *