在线修改大表为分区表或更改其索引

此博客适合工作中经常遇到修改大表为分区表且不停业务的情况后修改分区表的全局索引为分区索引

exec dbms_redefinition.can_redef_table('ZXDBM_APPSTORE', 'T1', dbms_redefinition.cons_use_rowid);--查表是否可以做重定义
exec dbms_redefinition.start_redef_table(uname =>'ZXDBM_APPSTORE', orig_table => 'T1', int_table=> 'T2',
col_mapping  => null, options_flag => DBMS_REDEFINITION.CONS_USE_ROWID); --同步初始化数据到中间表，中间表是会占用同样的空间


VAR V_NUM NUMBER
 BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ZXDBM_APPSTORE', 'T1', 'T2',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, :V_NUM, TRUE);
--DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
--   uname                    IN  VARCHAR2,
--   orig_table               IN  VARCHAR2,
--   int_table                IN  VARCHAR2,
--   copy_indexes             IN  PLS_INTEGER := 1,
--   copy_triggers            IN  BOOLEAN     := TRUE,
--   copy_constraints         IN  BOOLEAN     := TRUE,
--   copy_privileges          IN  BOOLEAN     := TRUE,
--   ignore_errors            IN  BOOLEAN     := FALSE,
--   num_errors               OUT PLS_INTEGER,
--   copy_statistics          IN  BOOLEAN     := FALSE, 
--   copy_mvlog               IN  BOOLEAN     := FALSE); 
END;
/ 

print v_num

若是返回为0，则直接下一步sync_interim_table.若不为0，那么需要验证错误的操作

--查询没能成功的ddl

select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

exec dbms_redefinition.sync_interim_table('ZXDBM_APPSTORE', 'T1', 'T2');--同步差异数据为了下一步更快
exec dbms_redefinition.finish_redef_table('ZXDBM_APPSTORE', 'T1', 'T2');--最后同步数据以及更新相关依赖例如索引
exec dbms_redefinition.abort_redef_table('ZXDBM_APPSTORE', 'T1', 'T2'); --退出表重定义
 

具体实验步骤如下：

SQL>  create table t1(id number);


Table created.


SQL> create table t2 as select * from t1;


Table created.


SQL>  select count(*) from t1;


  COUNT(*)
----------
         0


SQL>  select count(*) from t2;


  COUNT(*)
----------
         0


SQL> create index ind_t1 on t1(id);


Index created.


SQL>  create index ind_t2 on t2(id);


Index created.


SQL> begin 
for i in 1 .. 100 loop
insert into t1 values(i); 
commit;
end loop;
end; 
/  2    3    4    5    6    7  


PL/SQL procedure successfully completed.


SQL> exec dbms_redefinition.can_redef_table('ZXDBM_APPSTORE', 'T1', dbms_redefinition.cons_use_rowid);


PL/SQL procedure successfully completed.


SQL> exec dbms_redefinition.start_redef_table(uname =>'ZXDBM_APPSTORE', orig_table => 'T1', int_table    => 'T2',

col_mapping  => null, options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);


PL/SQL procedure successfully completed.


SQL>  select count(*) from t1;


  COUNT(*)
----------
       100


SQL>  select count(*) from t2;


  COUNT(*)
----------
       100


SQL> select index_name,status,table_name from user_indexes where table_name like 'T_';


INDEX_NAME                     STATUS   TABLE_NAME
------------------------------ -------- ------------------------------
IND_T1                         VALID    T1
I_SNAP$_T2                     VALID    T2
IND_T2                         VALID    T2


SQL> begin 
for i in 101 .. 150 loop
insert into t1 values(i); 
commit;
end loop;
end; 
/  2    3    4    5    6    7  


PL/SQL procedure successfully completed.


SQL> exec dbms_redefinition.sync_interim_table('ZXDBM_APPSTORE', 'T1', 'T2');


PL/SQL procedure successfully completed.


SQL> select count(*) from t1;


  COUNT(*)
----------
       150


SQL> select count(*) from t2;


  COUNT(*)
----------
       150


SQL>  select index_name,status,table_name from user_indexes where table_name like 'T_';


INDEX_NAME                     STATUS   TABLE_NAME
------------------------------ -------- ------------------------------
IND_T1                         VALID    T1
I_SNAP$_T2                     VALID    T2
IND_T2                         VALID    T2


SQL> begin 
for i in 151 .. 200 loop
insert into t1 values(i); 
commit;
end loop;
end; 
/  2    3    4    5    6    7  


PL/SQL procedure successfully completed.


SQL> exec dbms_redefinition.finish_redef_table('ZXDBM_APPSTORE', 'T1', 'T2');


PL/SQL procedure successfully completed.


SQL>  select count(*) from t1;


  COUNT(*)
----------
       200


SQL>  select count(*) from t2;


  COUNT(*)
----------
       200


SQL> select index_name,status,table_name from user_indexes where table_name like 'T_';


INDEX_NAME                     STATUS   TABLE_NAME
------------------------------ -------- ------------------------------
IND_T1                         VALID    T2
IND_T2                         VALID    T1


SQL> begin 
for i in 201 .. 250 loop
insert into t1 values(i); 
commit;
end loop;
end; 
/  2    3    4    5    6    7  


PL/SQL procedure successfully completed.


SQL> exec dbms_redefinition.abort_redef_table('ZXDBM_APPSTORE', 'T1', 'T2');


PL/SQL procedure successfully completed.


SQL>  select count(*) from t1;


  COUNT(*)
----------
       250


SQL> select count(*) from t2;


  COUNT(*)
----------
         0


SQL> select index_name,status,table_name from user_indexes where table_name like 'T_';


INDEX_NAME                     STATUS   TABLE_NAME
------------------------------ -------- ------------------------------
IND_T1                         VALID    T2
IND_T2                         VALID    T1