ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Run Out Of Space On Undo Tablespace Using Import/Export DataPump

Run Out Of Space On Undo Tablespace Using Import/Export DataPump

原创 Linux操作系统 作者:spider0283 时间:2011-09-12 10:37:22 0 删除 编辑
 [ID 735366.1]

 修改时间 23-MAY-2011     类型 HOWTO     状态 PUBLISHED 

In this Document
  Goal
  Solution
  References


Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.4 - Release: 10.1 to 10.2
Information in this document applies to any platform.

Goal

Checked for relevance on 10-25-2010

With the old Import utility there is the option of using the parameters "buffer" and "commit=y".

That way, there are lower chances of running into issues with the Undo tablespace. Is there anything similar in Import DataPump or it's necessary to increase the Undo tablespace?

An example experiencing issues is when using DataPump to re-organize tables.

Solution

Unlike the traditional Export and Import utilities, which used the BUFFER, COMMIT, COMPRESS, CONSISTENT, DIRECT and RECORDLENGTH parameters, DataPump needs no tuning to achieve maximum performance.

DataPump "chooses" the best method to ensure that data and metadata are exported and imported in the most efficient manner.  Initialization parameters should be sufficient upon installation.

However, you can get

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

during the Import (impdp) if indexes are present in some cases.

Impdp maintains indexes during import by default and does not use direct_path if tables and indexes
are already created.  However, if there is no index to enforce constraints and you specify 

access_method=direct_path 

with the DataPump Import command line, DataPump can use direct path method to do the import.

To get around potential issues with the UNDO tablespace in this case:

- load data by direct path by disabling primary key constraint (using ALTER TABLE ... MODIFY CONSTRAINT ... DISABLE NOVALIDATE) and using access_method=direct_path.
-after loading data, enable primary key constraint (using ALTER TABLE ... MODIFY CONSTRAINT ... ENABLE VALIDATE)


References

NOTE:413965.1 - Oracle DataPump Quick Start
NOTE:727894.1 - Import Data Pump Exhausts Undo Tablespace - ORA-30036
BUG:4035188 - EXCESSIVE UNDO AND TEMP GENERATION WHEN INSERT INTO INDEXED TABLE AS SELECT
BUG:4586712 - A LARGE AMOUNT OF UNDO IS USED AT IMPORT BY IMPDP

显示相关信息 相关的


产品
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
错误
ORA-30036

返回页首返回页首

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/38267/viewspace-707197/,如需转载,请注明出处,否则将追究法律责任。

下一篇: NFSEN NETFLOW系统
请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    627655