UNDO SIZE

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 ?



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)