Tuesday, August 7, 2012

Redefining Tables Online.


Starting Oracle 11g database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables. For example, you can rebuild a table or/and change a table's definition online, when it's accessed by application or users. Same way you can partition a previously non-partitioned table online, when it's accessed by OLTP application(s).
Example:
connect as sysdba
grant execute on DBMS_REDEFINITION to MYSCHEMA;
grant create any table to MYSCHEMA;
grant alter any table to MYSCHEMA;
grant drop any table to MYSCHEMA;
grant lock any table to MYSCHEMA;
grant select any table to MYSCHEMA;
grant create any trigger to MYSCHEMA;
grant create any index to MYSCHEMA;
keep the original table DDL definition, including its indexes, constraints, triggers, etc.:
connect to MYSCHEMA
execute DBMS_REDEFINITION.CAN_REDEF_TABLE('MYSCHEMA', 'MYTABLE');
create table MYTABLE_TMP as select * from MYTABLE where 1=2;
execute DBMS_REDEFINITION.START_REDEF_TABLE('MYSCHEMA','MYTABLE','MYTABLE_TMP');
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('MYSCHEMA', 'MYTABLE','MYTABLE_TMP',
   DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
execute DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MYSCHEMA','MYTABLE','MYTABLE_TMP');
execute DBMS_REDEFINITION.FINISH_REDEF_TABLE('MYSCHEMA','MYTABLE','MYTABLE_TMP');
drop table MYTABLE_TMP;
Make sure all indexes, triggers, constraints were created/applied on the MYTABLE.

No comments: