关于impdp过程出现大量undo问题

显示全部楼层 · 2010-3-1 11:06:28

向1个1T的分区表里导入3个分区的数据，dump文件400G，数据库非归档模式，那个表和index都是nologging。
再导入初期存放data的tablespace和temp一直在增长，undo不变。产生redo很少。
在status查询导入行数和导出一致时，开始产生大量redo，过一段时间undo100%,报错
ORA-39171: Job is experiencing a resumable wait.
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
请问是哪里产生了这么多的undo呢？如何能减少它？
请教各位高人，感谢。
导入脚本
directory=Dump1
dumpfile=MES_exp01.dmp,
MES_exp02.dmp,
MES_exp03.dmp
logfile=mes_imp01.log
REMAP_SCHEMA = MESPRD:PKG_MESPRD
parallel=3
REMAP_TABLESPACE= TS_DMESDCL_01:PKG_MES_DAT,

TS_DMESMST_01:PKG_MES_DAT,

TS_DMESHIS_01:PKG_MES_DAT,

TS_DMESTRX_01:PKG_MES_DAT,

TS_IMESDCL_01:PKG_MES_DAT,

TS_IMESMST_01:PKG_MES_DAT,

TS_IMESHIS_01:PKG_MES_DAT,

TS_IMESTRX_01:PKG_MES_DAT

exclude=INDEX
TABLE_EXISTS_ACTION = APPEND

千问 · 2010-3-1 11:06:28

加大undo

千问 · 2010-3-1 11:06:28

加了很大了我undo 100G 竟然还是不够

千问 · 2010-3-1 11:06:28

顶求高人解答

千问 · 2010-3-1 11:06:28

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.

你可能少了一个constraint(包含PK)
EXCLUDE=CONSTRAINT.
导入后重建index,constraint.

千问 · 2010-3-1 11:06:28

赞同LS的看法！

千问 · 2010-3-1 11:06:28

原帖由 jackli00 于 2011-6-28 11:27 发表
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.

你可能少了一个constraint(包含PK)
EXCLUDE=CONSTRAINT.
导入后重建index,constraint.
请问是维护PK导致的产生大量undo，还是只要有index就会产生？
我disable constraints同时还需要unusable unique index吗？
如果不禁用的话，能估算出大概需要多少undo 表空间吗？
非常感谢。

千问 · 2010-3-1 11:06:28

要不挖掘下？