impdp 導(dǎo)入數(shù)據(jù)報錯 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

已經(jīng)自動擴(kuò)展到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> 

這里可能會出現(xiàn)添加一個tempfile還不夠用的現(xiàn)象，取決于導(dǎo)入數(shù)據(jù)數(shù)量的大小。因?yàn)閕mpdp導(dǎo)入的是邏輯數(shù)據(jù)會吃內(nèi)存。

導(dǎo)入完成后，再釋放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; 

至此，完畢！