we can update whole column of big table with no downtime with dbms_redefinition and col_mapping
there is an example:
find table with lots of rows for test and make a copy:
create table my_table nologging as select * from big_table;
create interim table for redefinition and add column to remap data need to be updated, also set default value for column we need to update:
create table my_table_redef as select * from my_table where 1=2; alter table my_table_redef add ( CUSTOMDESCRIPTION_TMP varchar2(255)); alter table my_table_redef MODIFY (CUSTOMDESCRIPTION default 'A'); select count (*) from my_table; 40947444
do redefinition with remap of CUSTOMDESCRIPTION to CUSTOMDESCRIPTION_TMP:
EXEC DBMS_REDEFINITION.can_redef_table(USER, 'MY_TABLE'); begin DBMS_REDEFINITION.start_redef_table( uname => USER, orig_table => 'MY_TABLE', int_table => 'MY_TABLE_REDEF', col_mapping =>'ID ID,FIELDNAME FIELDNAME,NEWVALUE NEWVALUE,OLDVALUE OLDVALUE,HISTORY_ID HISTORY_ID,CUSTOMDESCRIPTION CUSTOMDESCRIPTION_TMP, INTERNALVALUE INTERNALVALUE', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID); end; EXEC DBMS_REDEFINITION.sync_interim_table(USER, 'MY_TABLE', 'MY_TABLE_REDEF'); EXEC DBMS_REDEFINITION.finish_redef_table(USER, 'MY_TABLE', 'MY_TABLE_REDEF');
drop old column:
alter table my_table_redef drop column CUSTOMDESCRIPTION_TMP;
check:
select count (*),CUSTOMDESCRIPTION from MY_TABLE group by CUSTOMDESCRIPTION; 40947444 A
and dont forget to reset default value
alter table MY_TABLE modify CUSTOMDESCRIPTION default null;