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
robajohnson
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:

directory=backup
dumpfile=XLG.dmp
tables=CHRI_EVENTS
content=data_only
table_exists_action=truncate
exclude=constraint,index

Here is where it gets interesting, if you get the job status it says:
Worker 1 Status:
State: EXECUTING
Object Schema: LD2
Object Name: CHRI_EVENTS
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
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;
COUNT(*)
----------
0

And...

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

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
robajohnson
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: Mon Dec 05 06:40:51 CST 2016

Total time taken to generate the page: 0.10069 seconds