oracle在线重定义表

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‘,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‘,??????PARTITION?tb_cablecheck_equipment_p4?VALUES? LESS?THAN(MAXVALUE));

第三步：开始进行重定义

1 2 3 begin??dbms_redefinition.start_redef_table(‘scott‘,‘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‘,????DBMS_REDEFINITION.CONS_ORIG_PARAMS,?TRUE,?num_errors);??END;

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

1 2 3 begindbms_redefinition.sync_interim_table(‘scott‘,‘tb_cablecheck_equipment_bak2‘);end;

第六步：完成重定义

1 2 3 begindbms_redefinition.finish_redef_table(‘scott‘,‘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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

select * from tb_cablecheck_equipment_bak e 第一步：验证表是否能被在线重定义 验证是否能按主键重定义（默认，最后一次参数可以不加）