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