oracle数据库impdp报错处理ORA-39083、ORA-00959、ORA-39171、ORA-01653

来源：互联网发布：淘宝什么周几流量大编辑：程序博客网时间：2024/05/16 17:55

一、oracle数据库备份

$expdp \"\/ as sysdba\" schemas=HPRPRD001 directory=easback dumpfile=easexpdp20140811AM.dmp logfile=easexpdp20140811AM.log

二、数据库的恢复

1、sqlplus执行创建用户、文件控件、tmp空间
create tablespace "EAS_D_HPRTST140811_STANDARD" datafile '/oradata1/oradata/EAS_D_HPRTST140811_STANDARD.ORA' size 2000M autoextend on;

create temporary tablespace "EAS_T_HPRTST140811_STANDARD" tempfile '/oradata1/oradata/EAS_T_HPRTST140811_STANDARD.DBF' size 500M autoextend on;

create user HPRTST140811 identified by kingdee default tablespace EAS_D_HPRTST140811_STANDARD temporary tablespace EAS_T_HPRTST140811_STANDARD;

grant create session,create table,create procedure,create sequence,create trigger,create view,SELECT ANY DICTIONARY,CREATE MATERIALIZED VIEW,alter profile
to HPRTST140811;

2、导入备份出来的数据库文件
oracle用户下执行：
impdp \"\/ as sysdba\" directory=easback dumpfile=easexpdp20140811AM.dmp logfile=indp20140811AMimpdp.log remap_schema=HPRPRD001:HPRTST140811 remap_tablespace=EAS_D_HPRPRD001_STANDARD:EAS_D_HPRTST140811_STANDARD,EAS_T_HPRPRD001_STANDARD:EAS_D_HPRTST140811_STANDARD EXCLUDE=STATISTICS

----------------------------------------------------------------------------------------------------------------------------------------------

出现报错，如下：
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'EAS_D_HPRPRD001_STANDARD' does not exist
Failing sql is:
CREATE TABLE "HPRTST140811"."T_CSL_INTERCHECKSOLUTIONENTRY" ("FID" VARCHAR2(44 B
YTE) NOT NULL ENABLE, "FTEMGROUP" NVARCHAR2(240), "FTEMNUMBER" NVARCHAR2(240), "
FTEMNAME" NVARCHAR2(240), "FDIFFMODE" NVARCHAR2(160), "FITEMID" NVARCHAR2(160),
"FITEMNAME" NVARCHAR2(160), "FDATAELEMENT" NVARCHAR2(160), "FONLOANDIRECTION" NV
ARCHAR2(160), "FCHECKSOLUTIONID" VARCHAR2(44 BYTE) N
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'EAS_D_HPRPRD001_STANDARD' does not exist
Failing sql is:
CREATE TABLE "HPRTST140811"."T_CSL_ELIMINVESTDATAMAINTAIN" ("FCOMPANYID" VARCHAR
2(44 BYTE), "FTARGETCOMPANYID" VARCHAR2(44 BYTE), "FINVESTDATE" TIMESTAMP (6), "
FCURRENCYID" VARCHAR2(44 BYTE), "FHONOR" NUMBER(21,6), "FHONORSUM" NUMBER(21,6),
"FACCUMULATIONFOUND" NUMBER(21,6), "FUNSHAREDPROFIT" NUMBER(21,6), "FSURPLUSFOU
ND" NUMBER(21,6), "FOTHERITEM" NUMBER(21,6), "FREMAR

经检查是由于impdp的语句中多了空格导致，清除多余空格后正常执行。

----------------------------------------------------------------------------------------------------------------------------------------------

执行一半又报以下错误：
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table T_BAS_MULTIAPPROVE by 8 in tablespace EAS_D_HPRTST140811_STANDARD

经检查，发现原数据文件EAS_D_HPRTST140811_STANDARD.ORA已达到32G最大限制，最终通过另外开启sqlplus添加数据文件解决

alter tablespace "EAS_D_HPRTST140811_STANDARD" add datafile '/oradata1/oradata/EAS_D_HPRTST140811_STANDARD02.ORA' size 2000M autoextend on;

添加完数据文件后，之前暂停的导入进程会继续执行直至导入成功。

心得：遇到问题不必惊慌，做到以下几点很有必要：

1、语句编写要规范，哪怕是copy过来的也要仔细核对每一个字符，包括空格；

2、出现报错不要紧，仔细分析报错信息就能找到解决办法；

3、另外多查一些参考资料，听取前人经验；

4、记录处理方法及过程，以作备用，亦可以帮助他人。

0 0