How to calculate undo needed for operation (alter table add column)
722632Sep 15 2009 — edited Sep 24 2009Hello everyone!
There is a problem to calculate an approximate space needed in UNDO tablespace for certain operation. The operation is adding a new column to table with default value: " alter table SOME_TABLE ADD ("Test" NUMBER(1)); ". I tested that DML-statement on test table and it took over 3 hours to complete, and needed about 25Gb of UNDO tablespace for undo-data. Table size is ~6Gb and it has two indexes ~3Gb+4Gb. Table has ~270mln rows.
So the questions are:
1. Is it possible to calculate amount of free space needed in UNDO-tablespace FOR THAT SPECIFIC OPERATION(add column) based on table+indexes size/row count? (e.g. we need about 2x of table+indexes size for undo for that kind of operation)
2. Can we reduce the tablespace size needed in any way? (e.g. "create table as select" or in any other way)
P.S. I calculated recommended undo-tablespace size for that db, and it came somewhere about 25GB, but that's not what I need.
P.P.S And actualy, what is the best practice of operating with large tables? (alter add column)
DBMS is Oracle 10g release 10.2.0.4.
any thoughts?
Edited by: user8731822 on Sep 16, 2009 2:03 AM