在线重定义-操作步骤
第一步：验证表是否能被在线重定义
验证是否能按主键重定义（默认，最后一次参数可以不加）
| 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; |