 DATA PUMP using up UNDO! (and temp) [message #188757] |
Mon, 21 August 2006 09:24  |
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!
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
