| UNDO tablespace is full [message #566989] |
Fri, 21 September 2012 23:27  |
 |
juhi_23
Messages: 4
Registered: September 2012
Location: BANGALORE
|
Junior Member |
|
|
Hi All,
During export / import process if my UNDO tablespace gets full and if don't want to increase the size (or no adding of file) then how can i tackle the situation. Is there any parameter?
Actually, this is an interview question, asked to me.
Thanks is advance
Regards,
Juhi
|
|
|
|
|
|
|
|
| Re: UNDO tablespace is full [message #567001 is a reply to message #566999] |
Sat, 22 September 2012 02:56  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I didn't ask why the interviewer asks for this (it is interviewer job to ask for everything even non-sense question) but what is your opinion/answer and why this opinion/answer. What do you think about the question and its answer?
Any direct answer to this question will be useless for you because there are million of such questions an interview can ask you and you will most likely never have this one again. But knowing how to answer is better and this starts by knowing how you currently answer and think.
Regards
Michel
[Edit: typos]
[Updated on: Mon, 10 December 2012 01:17] Report message to a moderator |
|
|
|
|
|
| Re: UNDO tablespace is full [message #567005 is a reply to message #567003] |
Sat, 22 September 2012 05:01 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I wouldn't expect an export to generate UNDO, but it may use UNDO generated by other sessions to ensure read consistency. If an export is unable to see an old version of a row in UNDO because UNDO is too small, then you either need to make export faster, or export less data. You can make export faster by exporting to a local (not network) SSD, make sure you are using DataPump and not Original Export and probably tweak a few other parameters. You can export less data by exporting one table at a time or perhaps using filters to export part of a table. Realistically though, what you would really do is to wait for a quiet period when there are few transactions to perform the export.
Import will generate undo in Conventional mode (ie. not direct path). If using the Original Import, you could use the COMMIT parameter to commit as it loads - that would free up some UNDO. DataPump does not offer a COMMIT parameter, so you would have to either import less data (eg. one table at a time) or use DirectPath DataPump import instead. If using DirectPath, I wouldn't be surprised if index maintenance generated UNDO, so if UNDO filled up that way then you could drop the indexes first.
There's probably lots of other factors as well. I don't think there is a succinct answer to this one; it depends on whether you are talking about Import or Export, DataPump or Original, Direct Path or Conventional, single table or many tables. It's possible (though not likely) that this was the interviewer's intention: to see haw many contributing factors you can think of and explain.
Ross Leishman
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: UNDO tablespace is full [message #572328 is a reply to message #572284] |
Mon, 10 December 2012 16:45 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
From one of our development databases which is massively quiet in the leadup to Christmas with most staff on leave.
SQL> select sum(blocks) from dba_data_files where tablespace_name='UNDOTBS';
SUM(BLOCKS)
-----------
65536
SQL> select sum(blocks) from dba_undo_extents;
SUM(BLOCKS)
-----------
61256
SQL> select status, count(*) nb, sum(blocks) blocks from dba_undo_extents group by status;
STATUS NB BLOCKS
--------- ---------- ----------
EXPIRED 314 12744
UNEXPIRED 520 48512
Interestingly, one of our production instances matches your statistics.
SQL> select sum(blocks) from dba_data_files where tablespace_name='UNDOTS';
SUM(BLOCKS)
-----------
3403016
SQL> select sum(blocks) from dba_undo_extents;
SUM(BLOCKS)
-----------
26328
SQL> select status, count(*) nb, sum(blocks) blocks from dba_undo_extents group by status;
STATUS NB BLOCKS
--------- ---------- ----------
EXPIRED 352 2816
UNEXPIRED 254 23384
ACTIVE 1 128
It looks as though I should spend some time and get this production 'undo' space a bit smaller.
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
