oracle ora39171,impdp 导入数据报错 ORA-01652

Oracle 11.2.0.4.0

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

ORA-39171: Job is experiencing a resumable wait.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

ORA-39171: Job is experiencing a resumable wait.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

查看temp 表空间

SQL> select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files

2  ;

FILE_NAME

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

MB AUT TABLESPACE_NAME

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

/data0/oracle/oradata/hldbm/hldbm/temp01.dbf

32767 YES TEMP

已经自动扩展到32G了，db_block为8192,dbf只能增长到32G。

解决方法：

给temp表空间添加tempfile并文件开启autoextend。

SQL> alter tablespace temp add tempfile '/data0/oracle/oradata/hldbm/hldbm/temp02.dbf' size 1000M autoextend on;

Tablespace altered.

SQL>

这里可能会出现添加一个tempfile还不够用的现象，取决于导入数据数量的大小。因为impdp导入的是逻辑数据会吃内存。

导入完成后，再释放temp空间。

释放过程：

create temporary tablespace TEMP01

TEMPFILE '/data0/oracle/oradata/hldbm/hldbm/tempfile01.dbf' SIZE 1000M

REUSE AUTOEXTEND ON;

alter database default temporary tablespace TEMP01;

drop tablespace temp including contents and datafiles;

create temporary tablespace TEMP

TEMPFILE '/data0/oracle/oradata/hldbm/hldbm/temp01.dbf' SIZE 1000M

REUSE AUTOEXTEND ON;

alter database default temporary tablespace TEMP;

drop tablespace TEMP01 including contents and datafiles;

至此，完毕！