当前位置 : 主页 > 数据库 > mysql >

用DBMS_REDEFINITION将普通表转换为分区表

来源:互联网 收集:自由互联 发布时间:2021-04-08
1. DBMS_REDEFINITION简介 要将普通表转换为分区表,Oracle官方给出四种方案: 导入/导出; insert select ; 交换分区法; 在线重定义(DBMS_REDEFINITION)。 这些方案的思路都是创建一个新的分区

1. DBMS_REDEFINITION简介

要将普通表转换为分区表,Oracle官方给出四种方案:

  • 导入/导出;
  • insert … select …;
  • 交换分区法;
  • 在线重定义(DBMS_REDEFINITION)。

这些方案的思路都是创建一个新的分区表,然后把旧表的数据转移到新表上面,接着转移相应的依赖关系,最后进行表的重命名,把新表和旧表rename。与前三种方案相比,DBMS_REDEFINITION几乎不影响旧表的正常使用,因此也逐渐成为目前普遍使用的转换分区表的方案。

以下以项目中某个大表TP_CARD_INFO(约1200万条记录)为例,说明将普通表转换为分区表的操作步骤。

2. 检查普通表能否进行分区

基于主键来确认:

SQL> begin
  2  DBMS_REDEFINITION.CAN_REDEF_TABLE('HSADM', 'TP_CARD_INFO', Dbms_Redefinition.cons_use_pk);
  3  end;
  4  /
PL/SQL procedure successfully completed

无错误输出表示可以。

3. 创建分区表

按主键分区,每个分区不超过200万条记录:

create table TP_CARD_INFO_PART
(
  id              NUMBER(15) not null,
  card_num        VARCHAR2(32),
  card_num2      VARCHAR2(32),
  create_time    DATE,
  create_user    VARCHAR2(16),
  update_time    DATE,
  update_user    VARCHAR2(16),
  print_date      VARCHAR2(8),
  print_by        NVARCHAR2(40),
  print_unit_code VARCHAR2(16),
  print_unit_name NVARCHAR2(70),
  print_reason    NVARCHAR2(40),
  finger_absence  NVARCHAR2(10)
)
partition by range(ID)
(
  partition TP_CARD_INFO_01 values less than (2000000) tablespace HS_DAT,
  partition TP_CARD_INFO_02 values less than (4000000) tablespace HS_DAT,
  partition TP_CARD_INFO_03 values less than (6000000) tablespace HS_DAT,
  partition TP_CARD_INFO_04 values less than (8000000) tablespace HS_DAT,
  partition TP_CARD_INFO_05 values less than (10000000) tablespace HS_DAT,
  partition TP_CARD_INFO_06 values less than (12000000) tablespace HS_DAT,
  partition TP_CARD_INFO_99 values less than (MAXVALUE) tablespace HS_DAT
);

4. 迁移数据

SQL> exec DBMS_REDEFINITION.start_redef_table('HSADM', 'TP_CARD_INFO', 'TP_CARD_INFO_PART');
PL/SQL procedure successfully completed

整个过程用时为256秒。

5. 迁移权限对象

SQL> declare
  2  num_errors PLS_INTEGER;
  3  begin
  4  dbms_redefinition.copy_table_dependents('HSADM', 'TP_CARD_INFO', 'TP_CARD_INFO_PART',
  5  dbms_redefinition.cons_orig_params, TRUE, TRUE, TRUE, TRUE, num_errors);
  6  end;
  7  /
PL/SQL procedure successfully completed

整个过程用时为526秒。

6. 查询是否有错

SQL> select object_name, base_table_name, ddl_txt from  DBA_REDEFINITION_ERRORS;
OBJECT_NAME                                                                      BASE_TABLE_NAME                                                                  DDL_TXT
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------

无错误。

7. 结束重定义过程

begin
  2  dbms_redefinition.finish_redef_table('HSADM', 'TP_CARD_INFO', 'TP_CARD_INFO_PART');
  3  end;
  4  /
PL/SQL procedure successfully completed

整个过程用时为73秒。

8. 出现异常时退出

若重定义过程中出现异常,必须执行退出过程:

SQL> begin
  2  dbms_redefinition.abort_redef_table('HSADM', 'TP_CARD_INFO', 'TP_CARD_INFO_PART');
  3  end;
  4  /

网友评论