Home » RDBMS Server » Server Administration » Undo Tablespace Full (10g, SLES9)
Undo Tablespace Full [message #357958] Fri, 07 November 2008 07:13 Go to next message
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 #357962 is a reply to message #357958] Fri, 07 November 2008 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on many things like undo retention and undo guarantee parameters as well as content of the tablespace.

Regards
Michel
Re: Undo Tablespace Full [message #357964 is a reply to message #357958] Fri, 07 November 2008 07:24 Go to previous messageGo to next message
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 #357972 is a reply to message #357964] Fri, 07 November 2008 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which "alter table"?

Regards
Michel
Re: Undo Tablespace Full [message #357973 is a reply to message #357958] Fri, 07 November 2008 08:01 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
alter table owner.mytable
modify mycolumn char(13);

Changing from char(12) to char(13)
Re: Undo Tablespace Full [message #357974 is a reply to message #357973] Fri, 07 November 2008 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should not use CHAR but VARCHAR2.

With your statement, Oracle has to add a blank to all rows.

Regards
Michel
Re: Undo Tablespace Full [message #357975 is a reply to message #357958] Fri, 07 November 2008 08:04 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
I just did a full count - the table has 66 million records in it
Re: Undo Tablespace Full [message #357976 is a reply to message #357975] Fri, 07 November 2008 08:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd be a little suprised if it was this that's using up undospace, as DDL trasatinos cannot be rolled back.
Re: Undo Tablespace Full [message #357982 is a reply to message #357976] Fri, 07 November 2008 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DDL can't be rolled back because they are committed at the end if everything runs fine but during the process it should be able to roll back what it did if anything goes wrong, so it has to record undo changes for all modified blocks.

Regards
Michel
Re: Undo Tablespace Full [message #357985 is a reply to message #357958] Fri, 07 November 2008 08:31 Go to previous messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 #357997 is a reply to message #357990] Fri, 07 November 2008 11:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This table also has indexes that it has to maintain

Only if you have index on the column you modify, in this case, of course you should drop them before.

Regards
Michel
Re: Undo Tablespace Full [message #358000 is a reply to message #357991] Fri, 07 November 2008 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. Not easy, about ("column size"+"rowid size"+overhead)*"number of rows"+"other overhead" + wasted space to block rounding.
But of course there maybe row migration (due to increase in size of the rows) and depending on the number of migrated rows this may be (far) above.
Here's a quick test:
SQL> create table t (col char(12));

Table created.

SQL> insert into t select dummy from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> @mystat 'undo change vector size'

NAME                                                    VALUE
-------------------------------------------------- ----------
undo change vector size                               2469404

SQL> alter table t modify col char(13);

Table altered.

SQL> @mystat2 'undo change vector size'

NAME                                                    VALUE       DIFF
-------------------------------------------------- ---------- ----------
undo change vector size                             162542612  160073208

SQL> @calc 160073208/1000000

 160073208/1000000 = 160.073208

It took 160 bytes per row.

2. Nothing, it is a row per row process. but you could create a new table with "create table as select" drop the old one and rename the new one.
This is far far faster but you have to then recreate all indexes, grants, and so on and you need to have the double of the space.
SQL> create table t (col char(12));

Table created.

SQL> insert into t select dummy from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> @mystat 'undo change vector size'

NAME                                                    VALUE
-------------------------------------------------- ----------
undo change vector size                             167683724

SQL> create table t2 as select cast(col as char(13)) col from t;

Table created.

SQL> @mystat2 'undo change vector size'

NAME                                                    VALUE       DIFF
-------------------------------------------------- ---------- ----------
undo change vector size                             167713420      29696

SQL> select count(*) from t2;
  COUNT(*)
----------
   1000000

1 row selected.

SQL> set lines 65
SQL> desc t2
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 COL                                       CHAR(13)

SQL> drop table t;

Table dropped.

SQL> rename t2 to t;

Table renamed.

SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 COL                                       CHAR(13)

Regards
Michel


Re: Undo Tablespace Full [message #358238 is a reply to message #357958] Mon, 10 November 2008 05:02 Go to previous messageGo to next message
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 messageGo to next message
Michel Cadot
Messages: 64139
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 messageGo to next message
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
------------------------
Re: Undo Tablespace Full [message #362198 is a reply to message #362165] Mon, 01 December 2008 05:29 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: sga_max_size
Next Topic: ORA 01503 create controlfile failed
Goto Forum:
  


Current Time: Thu Dec 08 20:17:57 CST 2016

Total time taken to generate the page: 0.34021 seconds