Oracle在线重定义DBMS_REDEFINITION 普通表—>分区表
实验环境:RHEL 6.4 + Oracle 11.2.0.3
实验:在线重定义 普通表 为 分区表,包括主键对应的索引都改造为分区索引.
1,构造普通表t_objects
conn test1/test1; create table t_objects as select * from dba_objects; SQL> select count(1) from t_objects; COUNT(1) ---------- 468738 --t_objects建立主键和索引 alter table t_objects add constraint pk_objects primary key (created, object_id); create index i_objects on t_objects(object_id, STATUS); --表有主键,确认表可以重定义: SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(‘test1‘,‘t_objects‘); PL/SQL procedure successfully completed. --若表无主键 可以采用rowid重定义: SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(‘test1‘,‘t_objects‘,2); PL/SQL procedure successfully completed.
2,创建重定义需要的临时表
-- Create table create table T_OBJECTS_TEMP ( OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(128), SUBOBJECT_NAME VARCHAR2(30), OBJECT_ID NUMBER not null, DATA_OBJECT_ID NUMBER, OBJECT_TYPE VARCHAR2(19), CREATED DATE not null, LAST_DDL_TIME DATE, TIMESTAMP VARCHAR2(19), STATUS VARCHAR2(7), TEMPORARY VARCHAR2(1), GENERATED VARCHAR2(1), SECONDARY VARCHAR2(1), NAMESPACE NUMBER, EDITION_NAME VARCHAR2(30) )partition by range(created)( partition P20130601 values less than (TO_DATE(‘ 2013-06-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIAN‘)) tablespace DBS_D_GRNOPHQ, partition P20140607 values less than (TO_DATE(‘ 2014-06-07 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIAN‘)) tablespace DBS_D_GRNOPHQ, partition P20140731 values less than (TO_DATE(‘ 2014-07-31 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIAN‘)) tablespace DBS_D_GRNOPHQ );
3,开始重定义
exec DBMS_REDEFINITION.START_REDEF_TABLE(‘test1‘,‘t_objects‘,‘t_objects_temp‘);
注;若无主键不能这样重定义,需要指定以rowid重定义,示例如下:
SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE(‘test1‘,‘t_objects‘,‘t_objects_temp‘,null,2); PL/SQL procedure successfully completed.
4,开始拷贝表的属性(本次未做,因为这样转换的,索引不是分区索引)
DECLARE error_count pls_integer := 0; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( uname => ‘test1‘, orig_table => ‘t_objects‘, int_table => ‘t_objects_temp‘, ignore_errors => TRUE, num_errors => error_count); DBMS_OUTPUT.PUT_LINE(‘errors := ‘ || TO_CHAR(error_count)); END; /
--经实验,在开始重定义之后在临时表上创建local索引,重定义完成后,主键对应的索引也是分区索引;
alter table t_objects_temp add constraint pk_objects_temp primary key (created, object_id) using index local; create index i_objects_temp on t_objects_temp(object_id, STATUS) local;
5,同步数据
exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE (uname => ‘test1‘,orig_table => ‘t_objects‘,int_table => ‘t_objects_temp‘);
6,收集中间表的统计信息(选做)
EXEC DBMS_STATS.gather_table_stats(‘test1‘, ‘t_objects_temp‘, cascade => TRUE);
7,完成重定义
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => ‘test1‘,orig_table => ‘t_objects‘,int_table => ‘t_objects_temp‘);
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘TEST1‘,‘T_OBJECTS‘,‘T_OBJECTS_TEMP‘);
8,删除临时表
drop table t_objects_temp purge;
9,修改索引,约束名称和原表一致
alter index I_OBJECTS_TEMP rename to I_OBJECTS; alter index PK_OBJECTS_TEMP rename to PK_OBJECTS; alter table t_objects rename constraint pk_objects_temp to pk_objects;
10,ABORT_REDEF_TABLE使用
在FINISH_REDEF_TABLE之前,可以使用abort_redef_table停止重定义
SQL> select * from cat; TABLE_NAME TABLE_TYPE ------------------------------ ----------- MLOG$_T_OBJECTS TABLE T_OBJECTS TABLE T_OBJECTS_TEMP TABLE SQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE (‘test1‘,‘t_objects‘,‘t_objects_temp‘); PL/SQL procedure successfully completed. SQL> select * from cat; TABLE_NAME TABLE_TYPE ------------------------------ ----------- T_OBJECTS TABLE T_OBJECTS_TEMP TABLE
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。