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:
Post a Comment