Archive for May, 2008

DBMS_REDEFINITION – holy grail of Oracle

Monday, May 19th, 2008

DBMS_REDEFINITION allows an online redefinition of eg. table in heavy OLTP, 24×7 environment. Simple example of use(Oracle 10.2.0.3/Linux):

As normal user (in this case VNULL):
SQL> create table x ( id number(10) primary key, txt varchar2(16) );

Startup heavy DML traffic to database:
SQL> begin
for i in 100001..9000000 loop
insert /* +APPEND NOLOGGING */ into x (id, txt) values (i, NULL);
commit;
end loop;
end;
/

1
2
3
4
5

While the above snippet is running DBA has to ALTER TABLE to add single column (right now!), so he does:

SQL> alter table x add y char(1) default '1';
alter table x add y char(1) default '1'
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

So how to ALTER that “X” table? Use DBMS_REDEFINITION:

SQL> execute dbms_redefinition.can_redef_table('VNULL', 'X');
PL/SQL procedure successfully completed.
SQL> create table VNULL.X_TMP as select id, txt, '1' as y from vnull.x where 0=1;
Table created.
SQL>
execute dbms_redefinition.start_redef_table('VNULL','X','X_TMP');
PL/SQL procedure successfully completed.
SQL>
execute dbms_redefinition.sync_interim_table('VNULL','X','X_TMP');
PL/SQL procedure successfully completed.
SQL>
execute dbms_redefinition.finish_redef_table('VNULL','X','X_TMP');
PL/SQL procedure successfully completed.
SQL> drop table vnull.x_tmp;
Table dropped.
SQL> desc vnull.x;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(10)
TXT VARCHAR2(16)
Y CHAR(1)