Skip to content

quick way to update column online with no locks

quick way to update column online with no locks published on Комментариев к записи quick way to update column online with no locks нет

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;

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Primary Sidebar