Oracle在线重定义分区表

前言

在生产环境中，表会随着时间的增长变得越来越大，一些大表往往会对性能造成影响。
此时可以考虑对表进行分区操作。利用分区表的特性，将表中的数据按特定的规律分成多个区进行存储。
可以大大提高查询效率。此篇文章主要讲述了在线重定义分区表的操作方法。

使用在线重定义的一些限制条件

• 必须有足够的表空间来容纳表的两倍数据量。
• 主键列不能被修改。
• 表必须有主键。
• 必须在同一个用户下进行在线重定义。
• SYS和SYSTEM用户下的表无法进行在线重定义。
• 在线重定义无法采用nologging。
• 如果中间表有新增列，则不能有NOT NULL约束

测试准备

– 创建测试表(这里给了测试用户test dba权限)

create table par_tab tablespace datatbs as select * from dba_objects;

– 给表添加主键，重定义的默认方式表必须要有主键

alter table PAR_TAB
  add primary key (OBJECT_ID)
  using index 
  tablespace datatbs
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

– 检查表是否可以进行在线重定义 括号里写用户名和表名 DBMS_REDEFINITION.CONS_USE_PK代表by key方法，这也是默认的方法

BEGIN                                              
DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','PAR_TAB',DBMS_REDEFINITION.CONS_USE_PK);
END;
/

– 如果表没有主键会有如下报错

– 正常情况执行结果如下 检查通过

– 赋予操作用户足够的权限

GRANT CREATE SESSION, CREATE ANY TABLE,ALTER ANY TABLE,
      DROP ANY TABLE, LOCK ANY TABLE  ,SELECT ANY TABLE,
      CREATE ANY INDEX,CREATE ANY TRIGGER
      TO test;

– 赋予重定义的包的权限给用户

grant execute_catalog_role to test;

– 创建表结构一致的、配置好分区的中间表，可以配置max分区，也可以不配置

create table PAR_TAB_TMP
   partition by range(object_id)
   (
    partition p1 values less than (10001) TABLESPACE DATATBS,
    partition p2 values less than (20001) TABLESPACE DATATBS,
    partition p3 values less than (30001) TABLESPACE DATATBS,
  partition p4 values less than (40001) TABLESPACE DATATBS,
    partition p5 values less than (50001) TABLESPACE DATATBS,
    partition p6 values less than (60001) TABLESPACE DATATBS,
    partition p7 values less than (70001) TABLESPACE DATATBS,
    partition p8 values less than (80001) TABLESPACE DATATBS,
    partition p9 values less than (90001) TABLESPACE DATATBS,
    partition p10 values less than (100001) TABLESPACE DATATBS,
    partition p11 values less than (110001) TABLESPACE DATATBS,
    partition p12 values less than (120001) TABLESPACE DATATBS,
  partition p13 values less than (MAXVALUE) TABLESPACE DATATBS
   )
   as
   select * from PAR_TAB where 1 = 2;

– 备份源表
可以通过expdp单表导出方式，也可以通过SQL语句直接复制源表。
这里测试环境直接做简单的复制

create table PAR_TAB_BAK as select * from PAR_TAB;

– 统计索引信息
找出索引和对应的列，做好记录

select  table_owner, table_name , column_name, index_name
        from dba_ind_columns
        where table_owner='TEST' 
        and table_name='PAR_TAB';

在线重定义

– 通过start_redef_table进行在线重定义

EXEC dbms_redefinition.start_redef_table('TEST','PAR_TAB','PAR_TAB_TMP');

– 其实Oracle在线重定义是通过物化视图的LOG来实现的。做完这一步后，在中间表中也有了相同的数据

– 在重定义过程中，Oracle新建了两张表RUPD$_PAR_TAB和MLOG$_PAR_TAB

– 使用COPY_TABLE_DEPENDENTS复制表属性,并排除索引
(为什么排除索引？而不是直接复制索引？ 因为使用此方式复制的索引，会保留非分区表的索引类型，依然是GLOBAL的全局索引，并不会根据分区自动转换为LOCAL本地索引。)

DECLARE
  error_count pls_integer := 0;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname            => 'TEST',
    orig_table       => 'PAR_TAB',
    int_table        => 'PAR_TAB_TMP',
    copy_indexes     => 0,				-- 排除索引
    copy_triggers    => TRUE,
    copy_constraints => FALSE,
    copy_privileges  => TRUE,
    ignore_errors    => FALSE,
    num_errors       => error_count);
	
  dbms_output.put_line('errors := ' || to_char(error_count));
END;
/

– 重定义过程中有其他的DML语句操作在原始表上，Oracle通过SYNC_INTERIM_TABLE来做同步
– 插入一条记录到原始表中

insert into par_tab(owner,object_name,object_id) values ('TEST','TEST_TAB',100000)
commit;

– 这时中间表上是看不到的，这个操作会被记录在MLOG$_PAR_TAB中，

desc MLOG$_PAR_TAB

select OBJECT_ID,DMLTYPE$$ from MLOG$_PAR_TAB;

– 主动同步到PAR_TAB_TMP中

EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST', 'PAR_TAB','PAR_TAB_TMP');

– 此时，刚刚insert的数据已经同步到了中间表中。严格意义上来说，此步骤不是必须的，当完成在线重定义时，Oracle会自动同步数据，不过这样会加长表不可用的时间，所以还是建议单独做

– 完成在线重定义，在这一步中，要对原始表PAR_TAB以独占的方式锁定

EXEC dbms_redefinition.finish_redef_table('TEST','PAR_TAB','PAR_TAB_TMP');

– 创建索引
根据之前统计的索引信息，对索引进行重建。由于测试表没有创建索引，所以省略。但在生产环境中一定要对索引做好统计，方便后续重建索引操作。

完成后检查

– 操作完成后，发现RUPD$_PAR_TAB和MLOG$_PAR_TAB被自动删除，另外也可以看到重定义的效果了。原中间表由分区表变成了普通表 原来的普通表已经在线重定义成功变成了分区表。完成在线重定义