June 21, 2012

IMPDP consuming huge tempspace and failed with ORA-01652

There is a 130GB table, we want to partition it to improve performance.

We create an empty new parition table without index.
Then we expdp the previous table with clause: "CONTENT=data_only include=table":

Export: Release 11.2.0.3.0 - Production on Wed Jun 13 02:10:02 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_03": system/******** dumpfile=part%U.dmp logfile=exp_part.log 
tables=swxxxdm.T_XXX_TRAIL directory=exppart CONTENT=data_only include=table
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 148.6 GB
. . exported "SWAPADM"."T_XXX_TRAIL" 128.8 GB 914749810 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_03 is:
/server11/oracle/db1/exp/part01.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_03" successfully completed at 03:43:00

The dumpfile is 129gb big, and since we use "CONTENT=data_only include=table", so only table's row data export, no index and other things included:
server11.com[oracle]_db1 ls -lh part01.dmp
-rw-r----- 1 oracle oinstall 129G Jun 13 03:43 part01.dmp

Now we impdp the dumpfile into our newly created empty parition table, and here we encounter some error:
Import: Release 11.2.0.3.0 - Production on Wed Jun 13 04:52:07 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=part%U.dmp logfile=imp_part.log 
REMAP_table=T_XXX_TRAIL:T_XXX_TRAIL_PAR directory=exppart parallel=5 
table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP01
ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP01
ORA-39171: Job is experiencing a resumable wait.
.....
.....
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP01
ORA-31693: Table data object "SWXXXDM"."T_XXX_TRAIL_PAR" failed to load/unload 
and is being skipped due to error:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP01
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 38 error(s) at 08:29:42

The impdp consumend all temp tablespace then failed. It is not supposed to use such big temp tablespace. This doesn't make sense.
From below we can see the impdp SQL consumed all 16gb temp tablespace for sort oepration. Instead of importing data into target permenant tablesepace directly, the impdp job is trying to import all 129gb rows into temp tablespace first:
SQL> select se.username,se.sid,su.extents,su.blocks*8192/1024/1024 MB,tablespace,segtype,se.sql_id,sql_text from v$sort_usage su,v$session se,v$sql s
where su.session_addr=se.saddr and s.sql_id=se.sql_id order by su.blocks;
USERNAME    SID   EXTENTS     MB TABLESPACE TYPE        SQL_ID
-------- ------ --------- ------ ---------- ---- -------------
  SYSTEM     34     13685  14842     TEMP01 SORT 2w1k045t5xfp0
INSERT /*+ APPEND PARALLEL("T_XXX_TRAIL_PAR",5)+*/ INTO RELATIONAL("SWXXXDM"."T_XXX_TRAIL_PAR" NOT XMLTYPE) ("X_YEAR", "XXX_ID", "ORG", "JURIXXION_ID", "CATXX_ID", "ACXXNT_ID", "XXTYPE", "VERXXON_ID", "ACCOUNT_XXSDICTXXD", "BEG_XXX_FLAG", "AMT

Very wierd.

After searched on metalink, find two articles report samiliar issues:
Bug 12555010: IMPDP CONSUMES HUGE TEMP TABLESPACE WITH NETWORK LINK AGAINST PARTIONED TABLE
Bug : SUSPECT BEHAVIOR AT DATA PUMP NETWORK IMPORT OF A HUGE PARTITIONED TABLE


As yet none patches available and none workaround provided.

Later one my teammate tried to redo impdp without "parallel=5", then impdp job completed wihtout the temp issue.

Seems another bug caused by PARALLEL.

0 Comments:

Post a Comment