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