Home » RDBMS Server » Server Utilities » DATA PUMP using up UNDO! (and temp)
icon9.gif  DATA PUMP using up UNDO! (and temp) [message #188757] Mon, 21 August 2006 09:24 Go to next message
Messages: 9
Registered: February 2006
Location: Dallas Texas area
Junior Member

Hello all,

I hope this is not one of those real obvious ones that I have missed. I have been using data pump for about a year now and I really like it and I have not had any problems like this before.

I am loading what was a 4 gig table before export. It continues to eat up Undo and temp space at an incredible rate. This database is not live yet, I am practicing migrations so I can play around some.

Just for grins I have grown UNDO to 14 gig! 3 1/2 times the original size of the table and I am still halting due to UNDO being full. I am the ONLY user in the database and undo_retention has been set to 0.

I have also grown TEMP out to 10 gig and that has stopped the temp issues for now. Still, that is over twice the size of the base table.

All triggers and constraints are disabled and I have the import set to not reload them.

Here are the impdp parms:


Here is where it gets interesting, if you get the job status it says:
Worker 1 Status:
Object Schema: LD2
Object Name: CHRI_EVENTS
Completed Objects: 1
Completed Rows: 21,396,063
Completed Bytes: 5,340,084,392
Percent Done: 100
Worker Parallelism: 1

21 million rows and 5 gig loaded and it says one object complete but if you query:

SQL> Select count(*) from ld2.CHRI_EVENTS;


SQL> select bytes from dba_segments where owner='LD2' and segment_name='CHRI_EVENTS';

So it is using 6 gig in DBA segments but there are zero rows. Clearly it is not commiting.

I don't see anywhere in the docs where data pump has a commit=y like import did.
Does anyone know of one?

If not, it seems to me that this may be a serious shortcoming in data pump when working with large objects!
Re: DATA PUMP using up UNDO! (and temp) [message #188776 is a reply to message #188757] Mon, 21 August 2006 10:53 Go to previous message
Messages: 9
Registered: February 2006
Location: Dallas Texas area
Junior Member

I finally got it to finish...

. . imported "LD2"."CHRI_EVENTS" 4.973 GB 29595993 rows

But I had to take UNDO out to 20 gig to get a 4.9 gig table to load.

What is wrong with this picture?
Previous Topic: Some jobs stop running after some time
Next Topic: about 9.2.0 emp/imp error
Goto Forum:

Current Time: Sat Oct 22 22:53:44 CDT 2016

Total time taken to generate the page: 0.04735 seconds