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

[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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/38267/viewspace-707197/