Re: Which is the fast way to add a column with a value?

Hi Luis,

yes, the alter table *will* use the rollback segment.

You can avoid flooding your rollback segments by the following:

1. exp file=abc.dmp direct=y log=abc.log buffer=16777216 userid=whateverconnectstring in one line! The buffer is just for 1. a)!
2. a) If abc happens to contain a lot of migrated or chained rows, you should leave away the direct=y because direct export takes ages (we exported 30 million rows in conventional way in 1.5 h. This table contained 20 million migrated rows. The direct export took 10.5 h!)
3. truncate table abc;
4. a) for speeding up you can do alter table abc nologging;
5. alter table abc add (new_column varchar2 (1) default 'N' not null);
6. imp file=abc.dmp ignore=y log=abc_imp.log commit=y userid=whateverconnectstring in one line. The commit=y is just if you did 1. a)!
7. a) If you did 2. a), you *must* backup your tablespace where abc resides. Otherwise it will not be restorable despite archivelog mode! You *should* do alter table abc logging;

Martin

Luis Santos wrote:
>
> Hi,
>
> We have to add a column to a partitioned table. The table is over
> 12G in size, divided in 12 partiotion, of similar value (1G).
>
> The column must contain the value 'N' on it.
>
> We have limited rollback space.
>
> If we use a PL/SQL similiar to
>
> for reg in (select rowid from tab) loop
> update tab set new_column-'N' where rowid=reg.rowid
> (commit at each 20000 lines)
> end loop;
>
> the PL runs for days...
>
> Can we use ALTER TABLE tab ADD new_column char(1) default 'N'?
>
> Will this use rollback segment, as it is a DDL command?
>
> --
> Luis Santos
> Oracle DBA
>
> Sent via Deja.com
> http://www.deja.com/
Received on Sat Feb 17 2001 - 04:23:49 CST