Undo Tablespace Full [message #357958] |
Fri, 07 November 2008 07:13 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dirkm
Messages: 86 Registered: November 2008 Location: Centurion - South Africa
|
Member |
|
|
What happens in Oracle when the Undo space gets full ?
I tried to Google this, and gets lots of links about Undo, but haven't found one that talks about undo space getting full.
TIA
Dirk
|
|
|
|
Re: Undo Tablespace Full [message #357964 is a reply to message #357958] |
Fri, 07 November 2008 07:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dirkm
Messages: 86 Registered: November 2008 Location: Centurion - South Africa
|
Member |
|
|
I am doing an alter table on a large table, about 30 million records.
The alter started yesterday, about 22 hours ago, and is still running. From my queries, and from Enterprise Manager the alter still seems to be running (not hanging / not blocked, etc.). But I also see that my Undo space is full.
Is this affecting the alter table ? Why is the alter table taking so long ?
Dirk
|
|
|
|
|
|
|
|
|
Re: Undo Tablespace Full [message #357985 is a reply to message #357958] |
Fri, 07 November 2008 08:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dirkm
Messages: 86 Registered: November 2008 Location: Centurion - South Africa
|
Member |
|
|
Yes, I agree, it should be VARCHAR2, but unfortunately this is an old system, and we already tried this change, but the effect on our software was too big. We were not able to move from char to varchar2.
Seeing that this is still "during the process" - is the undo for this DDL being recorded, and is the alter now being affected by my Undo space being full ?
Dirk
|
|
|
Re: Undo Tablespace Full [message #357989 is a reply to message #357985] |
Fri, 07 November 2008 09:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Michel - of course - hadn't thought of that. Not enough sleep I suspect. Thanks.
Dirkm - As I understand it, the Undo tablespace is used in a cyclical fashion, to preserve the data in it for as long as possible - to help minimise things like ORA-1555 errors.
So you in a busy system, it should always look full.
If your transaction had actually used all of the available space and still needed more, then it would error.
Have a look on v$transaction for your session - the columns Used_Ublk and Used_Urec show you the amount of Undo that your ransaction is using.
|
|
|
Re: Undo Tablespace Full [message #357990 is a reply to message #357958] |
Fri, 07 November 2008 09:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dirkm
Messages: 86 Registered: November 2008 Location: Centurion - South Africa
|
Member |
|
|
Great, thank you very much.
And also an update - just now, as I was reading this thread, the alter table crashed, with the following error:
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS_NODE1'
I will investigate this, so that I can find a way to get around this.
Another question. This table also has indexes that it has to maintain. I suppose the changes in the indexes will also be logged in the Undo if they were not dropped before the alter table ?
|
|
|
Re: Undo Tablespace Full [message #357991 is a reply to message #357958] |
Fri, 07 November 2008 10:05 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dirkm
Messages: 86 Registered: November 2008 Location: Centurion - South Africa
|
Member |
|
|
Two Questions:
--------------
1. How would I calculate how much Undo space I need for this alter table ?
2. Is there anything I can do to minimise the amount of Undo needed when doing the alter table ?
|
|
|
|
|
Re: Undo Tablespace Full [message #358238 is a reply to message #357958] |
Mon, 10 November 2008 05:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dirkm
Messages: 86 Registered: November 2008 Location: Centurion - South Africa
|
Member |
|
|
Thank you very much for all your help with this.
Just 1 last question. What is the standard way of doing this, on a multimillion row table.
Drop the indexes, alter the table & recreate the indexes ? (if I don't want to recreate the table)
Dirk
|
|
|
Re: Undo Tablespace Full [message #358273 is a reply to message #358238] |
Mon, 10 November 2008 08:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
There is no standard way.
You have to possibilities, you can see the benefits and drawbacks of each ones, now you have to choose the way you want to do it depending on your environment.
If you don't which one to choose, make tests with the different techniques.
Regards
Michel
[Updated on: Mon, 10 November 2008 08:14] Report message to a moderator
|
|
|
Re: Undo Tablespace Full [message #362165 is a reply to message #357958] |
Mon, 01 December 2008 04:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dirkm
Messages: 86 Registered: November 2008 Location: Centurion - South Africa
|
Member |
|
|
Just some feedback on this one for those interested -
Table size: 66 million rows
I decided to disable the triggers, drop the indexes, alter the table, and then recreate the indexes. Here are the times it took, compared to the previous time where it took 25 hrs, in which the alter table also crashed due to insufficient UNDO space available:
Drop indexes:
-------------
Sun 08-Feb-2009 15:41:55
Sun 08-Feb-2009 15:41:56
(less than a minute)
Alter table:
------------
Sun 08-Feb-2009 15:44:14
Sun 08-Feb-2009 16:27:59
(43 min)
Recreate indexes:
-----------------
Sun 08-Feb-2009 19:06:55
Sun 08-Feb-2009 21:27:55
(2 hr 21 min)
------------------------
Total time: 3 hrs 4 min
------------------------
|
|
|
|