1, DDL

2, Call dbms_redefinition.can_redef_table()，check out INCIDENTSM1 can be redefined online

exec dbms_redefinition.can_redef_table('SC','INCIDENTSM1',dbms_redefinition.cons_use_pk);

3, Create an interim table under the same schema

INCIDENTSM1_create_table.sql

4, Create triggers,indexes and constraints on the interim table

select INDEX_NAME,TABLE_NAME,COLUMN_NAME from dba_ind_columns where TABLE_NAME='INCIDENTSM1' order by 1;
select owner,index_name,table_owner,table_name,INCLUDE_COLUMN from dba_indexes where table_name='INCIDENTSM1';

select owner,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints where table_name = 'INCIDENTSM1';
select owner,TRIGGER_NAME,TRIGGER_TYPE,TABLE_OWNER,TABLE_NAME from dba_triggers where TABLE_NAME='INCIDENTSM1';
no rows selected

create index : INCIDENTSM1_create_index.sql

5. Call dbms_redefinition.start_redef_table(),begin redefinition

exec dbms_redefinition.start_redef_table('SC','INCIDENTSM1','INCIDENTSM1_LG',NULL,dbms_redefinition.cons_use_rowid,NULL,NULL);

If START_REDEF_TABLE fails for any reason, you must call ABORT_REDEF_TABLE, otherwise subsequent attempts to redefine the table will fail.

DBMS_REDEFINITION.ABORT_REDEF_TABLE(('SC','INCIDENTSM1','INCIDENTSM1_LG',NULL);

6. Synchronize interim table
exec dbms_redefinition.sync_interim_table('SC', 'INCIDENTSM1', 'INCIDENTSM1_LG',NULL);

7. Copy all dependent objects of the original table to interim table

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SC','INCIDENTSM1','INCIDENTSM1_LG',0,TRUE,TRUE,TRUE,TRUE,TRUE);

DBMS_REDEFINITION.REGISTER_DEPEPENDENT_OBJECT('SC','INCIDENTSM1','INCIDENTSM1_LG',DBMS_REDEFINITION.CONS_INDEX,'SC','APPROVALLOGM1_2','APPROVALLOGM1_2_LG');
DBMS_REDEFINITION.REGISTER_DEPEPENDENT_OBJECT('SC','INCIDENTSM1','INCIDENTSM1_LG',DBMS_REDEFINITION.CONS_INDEX,'SC','APPROVALLOGM1_P','APPROVALLOGM1_P_LG');

8. Call dbms_redefinition.finish_redef_table(), finishe redefinition
exec dbms_redefinition.finish_redef_table('SC','INCIDENTSM1','INCIDENTSM1_LG');

#. Verify

9. Drop interim table
drop table INCIDENTSM1_LG ;

ITPub博客

dbms_redefinition 表的在线重定义

最新文章