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,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
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);
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');
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');
exec dbms_redefinition.finish_redef_table('SC','INCIDENTSM1','INCIDENTSM1_LG');
#. Verify
9. Drop interim table
drop table INCIDENTSM1_LG ;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11976525/viewspace-668629/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11976525/viewspace-668629/