OraFAQ Forum: Server Administration » Undo Tablespace Full

Home » RDBMS Server » Server Administration » Undo Tablespace Full (10g, SLES9)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Undo Tablespace Full [message #357958]

Fri, 07 November 2008 07:13

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

Report message to a moderator

Re: Undo Tablespace Full [message #357962 is a reply to message #357958]

Fri, 07 November 2008 07:19

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Undo Tablespace Full [message #357964 is a reply to message #357958]

Fri, 07 November 2008 07:24

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

Report message to a moderator

Re: Undo Tablespace Full [message #357972 is a reply to message #357964]

Fri, 07 November 2008 07:57

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Which "alter table"?

Regards
Michel

Report message to a moderator

Re: Undo Tablespace Full [message #357973 is a reply to message #357958]

Fri, 07 November 2008 08:01

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)

Report message to a moderator

Re: Undo Tablespace Full [message #357974 is a reply to message #357973]

Fri, 07 November 2008 08:04

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Undo Tablespace Full [message #357975 is a reply to message #357958]

Fri, 07 November 2008 08:04

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

Report message to a moderator

Re: Undo Tablespace Full [message #357976 is a reply to message #357975]

Fri, 07 November 2008 08:07

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.

Report message to a moderator

Re: Undo Tablespace Full [message #357982 is a reply to message #357976]

Fri, 07 November 2008 08:25

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Undo Tablespace Full [message #357985 is a reply to message #357958]

Fri, 07 November 2008 08:31

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

Report message to a moderator

Re: Undo Tablespace Full [message #357989 is a reply to message #357985]

Fri, 07 November 2008 09:15

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.

Report message to a moderator

Re: Undo Tablespace Full [message #357990 is a reply to message #357958]

Fri, 07 November 2008 09:58

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 ?

Report message to a moderator

Re: Undo Tablespace Full [message #357991 is a reply to message #357958]

Fri, 07 November 2008 10:05

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 ?

Report message to a moderator

Re: Undo Tablespace Full [message #357997 is a reply to message #357990]

Fri, 07 November 2008 11:10

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Undo Tablespace Full [message #358000 is a reply to message #357991]

Fri, 07 November 2008 11:32

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Undo Tablespace Full [message #358238 is a reply to message #357958]

Mon, 10 November 2008 05:02

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

Report message to a moderator

Re: Undo Tablespace Full [message #358273 is a reply to message #358238]

Mon, 10 November 2008 08:13

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

Re: Undo Tablespace Full [message #362198 is a reply to message #362165]

Mon, 01 December 2008 05:29

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback.

Regards
Michel

Report message to a moderator

Previous Topic:	sga_max_size
Next Topic:	ORA 01503 create controlfile failed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 20:41:29 CST 2024