oracle在线重定义表

select * from tb_cablecheck_equipment_bak e

第一步：验证表是否能被在线重定义

验证是否能按主键重定义（默认，最后一次参数可以不加）

1 2 3 4

begin   --dbms_redefinition.can_redef_table('scott','tb_cablecheck_equipment_bak');   dbms_redefinition.can_redef_table('scott','tb_cablecheck_equipment_bak',dbms_redefinition.cons_use_pk); end;

验证是否能按rowid重定义：

1 2 3 4

begin   --dbms_redefinition.can_redef_table('scott','tb_cablecheck_equipment_bak',2);   dbms_redefinition.can_redef_table('scott','tb_cablecheck_equipment_bak',dbms_redefinition.cons_use_rowid); end;

第二步：创建中间表

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

create table scott.tb_cablecheck_equipment_bak2(       equipment_id        NUMBER,       equipment_code      VARCHAR2(100),       equipment_name      VARCHAR2(500),       area_id             NUMBER,       address             VARCHAR2(500),       res_type_id         VARCHAR2(10),       res_type            VARCHAR2(20),       manage_area_id      VARCHAR2(100),       manage_area         VARCHAR2(100),       management_mode     CHAR(10),       isrelated           CHAR(1),       staff_id            VARCHAR2(20),       create_date         DATE,       ischecked           CHAR(20),       check_date          DATE,       operate_staff       NUMBER,       parent_area_id      NUMBER,       grid_id             NUMBER(20),       install_sbid        NUMBER,       install_sbbm        VARCHAR2(100),       install_dzbm        VARCHAR2(100),       longitude           VARCHAR2(50),       latitude            VARCHAR2(50),       station_id          VARCHAR2(50),       update_time         DATE,       check_complete_time DATE ) partition by range(create_date)(       PARTITION tb_cablecheck_equipment_p1 VALUES LESS THAN (TO_DATE('2013-08-01','YYYY-MM-DD')),       PARTITION tb_cablecheck_equipment_p2 VALUES LESS THAN(TO_DATE('2016-04-05', 'YYYY-MM-DD')),       PARTITION tb_cablecheck_equipment_p3 VALUES LESS THAN(TO_DATE('2016-12-13', 'YYYY-MM-DD')),       PARTITION tb_cablecheck_equipment_p4 VALUES LESS THAN(MAXVALUE) );

第三步：开始进行重定义

1 2 3

begin   dbms_redefinition.start_redef_table('scott','tb_cablecheck_equipment_bak','tb_cablecheck_equipment_bak2',null,2); end;

复制依赖对象

1 2 3 4 5 6

DECLARE     num_errors PLS_INTEGER;   BEGIN     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT', 'tb_cablecheck_equipment_bak','tb_cablecheck_equipment_bak2',     DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);   END;

第四步：同步中间表，保证数据的一致性

1 2 3

begin dbms_redefinition.sync_interim_table('scott','tb_cablecheck_equipment_bak','tb_cablecheck_equipment_bak2'); end;

第六步：完成重定义

1 2 3

begin dbms_redefinition.finish_redef_table('scott','tb_cablecheck_equipment_bak','tb_cablecheck_equipment_bak2'); end;

查看是否已被定义为分区表：

1	select * from tb_cablecheck_equipment_bak partition(tb_cablecheck_equipment_p1);

第七步：

1	drop table scott.tb_cablecheck_equipment_bak2;