impdp ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

對 之前存在表結構的表impdp匯入 超過500G的資料時報錯：ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1' 

DB Version:12.2.0.1

只好先執行Import > stop_job=immediate

檢視：

735366.1 - Run Out Of Space On Undo Tablespace Using Import/Export DataPump
727894.1 - Import Data Pump Exhausts Undo Tablespace - ORA-30036

Cause

Excess undo generation can occur when there is a Primary Key (PK) constraint present on the system.

Import datapump will perform index maintenance and this can increase undo usage especially if there is other DML occurring on the database).

Solution

Disable constraints for Primary Keys (PK) on the database during import datapump load.

This will reduce undo as index maintenance will not be performed.

用以下透過：

1、impdp先匯入資料(如果有index和約束的先drop)

1）CONTENT=METADATA_ONLY
TABLE_EXISTS_ACTION=REPLACE
EXCLUDE=CONSTRAINT
EXCLUDE=INDEX

2）CONTENT=DATA_ONLY

2、再建立索引，約束等

參考： 客戶在做impdp時，報“ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'”_ITPUB部落格