Oracle 分区表之在线重定义
Oracle
在线重定义表是从oracle9i开始提供的一个功能,能在线完成对一个表结构或存储的重定义,并且不影响当前应用的使用,是oracle高可用性中的一个很有用的特性。

一、介绍

「DBMS_REDEFINITION(在线重定义):」

  • 「支持的数据库版本」:Oracle Database - Enterprise Edition - Version 9.2.0.4 and later
  • 在线重定义是通过 「物化视图」 实现的。

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

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

「DBMS_REDEFINITION包:」

  • ABSORT_REDEF_TABLE:清理重定义的错误和中止重定义;
  • CAN_REDEF_TABLE:检查表是否可以进行重定义,存储过程执行成功代表可以进行重定义;
  • COPY_TABLE_DEPENDENTS:同步索引和依赖的对象(包括索引、约束、触发器、权限等);
  • FINISH_REDEF_TABLE:完成在线重定义;
  • REGISTER_DEPENDENTS_OBJECTS:注册依赖的对象,如索引、约束、触发器等;
  • START_REDEF_TABLE:开始在线重定义;
  • SYNC_INTERIM_TABLE:增量同步数据;
  • UNREGISTER_DEPENDENT_OBJECT:不注册依赖的对象,如索引、约束、触发器等;

二、实战

1、构建测试数据创建测试表空间和用户:

  1. sqlplus / as sysdba 
  2. create tablespace PAR; 
  3. create user par identified by par; 
  4. grant dba to par; 

创建测试表:

  1. sqlplus par/par 
  2. create table lucifer( 
  3. id number(8) PRIMARY KEY
  4. name varchar2(20) not null
  5. par_date date
  6. tablespace PAR; 
  7. comment on table lucifer is 'lucifer表'
  8. comment on column lucifer.name is '姓名'
  9. comment on column lucifer.par_date is '分区日期'
  10. create index id_name on lucifer(name) tablespace par; 

 

插入测试数据:

  1. sqlplus par/par 
  2. begin 
  3.   for i in 0 .. 24 loop 
  4.     insert into lucifer values 
  5.       (i, 
  6.        'lcuifer_' || i, 
  7.        add_months(to_date('2021-1-1''yyyy-mm-dd'), i)); 
  8.   end loop; 
  9.   commit
  10. end

可以看到,测试数据已经构建完成,接下来开始实战操作。

2、查看是否能够重定义

需提前确认表是否有主键,表空间是否足够:

  1. sqlplus / as sysdba 
  2. ##查看主键 
  3. select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'LUCIFER'

确认是否可以重定义,没有主键用 rowid:

  1. sqlplus / as sysdba 
  2. exec dbms_redefinition.can_redef_table('PAR''LUCIFER'); 

执行没有报错代表可以进行表的在线重定义。

3、创建中间表(分区表结构)

通过PL/SQL包一键生成分区表结构:

  1. sqlplus par/par 
  2. BEGIN 
  3.   ctas_par(p_tab        => 'lucifer'
  4.            p_part_colum => 'par_date'
  5.            p_part_nums  => 24, 
  6.            p_tablespace => 'par'); 
  7. END

创建中间分区表 lucifer_par:

  1. create table lucifer_par 
  2.   id       NUMBER(8), 
  3.   name     VARCHAR2(20), 
  4.   par_date DATE 
  5. partition BY RANGE(par_date)( 
  6. partition lucifer_P202101 values less than (TO_DATE(' 2021-02-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  7. partition lucifer_P202102 values less than (TO_DATE(' 2021-03-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  8. partition lucifer_P202103 values less than (TO_DATE(' 2021-04-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  9. partition lucifer_P202104 values less than (TO_DATE(' 2021-05-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  10. partition lucifer_P202105 values less than (TO_DATE(' 2021-06-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  11. partition lucifer_P202106 values less than (TO_DATE(' 2021-07-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  12. partition lucifer_P202107 values less than (TO_DATE(' 2021-08-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  13. partition lucifer_P202108 values less than (TO_DATE(' 2021-09-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  14. partition lucifer_P202109 values less than (TO_DATE(' 2021-10-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  15. partition lucifer_P202110 values less than (TO_DATE(' 2021-11-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  16. partition lucifer_P202111 values less than (TO_DATE(' 2021-12-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  17. partition lucifer_P202112 values less than (TO_DATE(' 2022-01-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  18. partition lucifer_P202201 values less than (TO_DATE(' 2022-02-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  19. partition lucifer_P202202 values less than (TO_DATE(' 2022-03-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  20. partition lucifer_P202203 values less than (TO_DATE(' 2022-04-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  21. partition lucifer_P202204 values less than (TO_DATE(' 2022-05-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  22. partition lucifer_P202205 values less than (TO_DATE(' 2022-06-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  23. partition lucifer_P202206 values less than (TO_DATE(' 2022-07-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  24. partition lucifer_P202207 values less than (TO_DATE(' 2022-08-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  25. partition lucifer_P202208 values less than (TO_DATE(' 2022-09-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  26. partition lucifer_P202209 values less than (TO_DATE(' 2022-10-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  27. partition lucifer_P202210 values less than (TO_DATE(' 2022-11-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  28. partition lucifer_P202211 values less than (TO_DATE(' 2022-12-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  29. partition lucifer_P202212 values less than (TO_DATE(' 2023-01-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIAN')) tablespace par, 
  30. partition lucifer_MAX values less than (maxvalue) tablespace par) 
  31.              enable row movement 
  32.              tablespace par; 

如上,唯一索引和约束不加,会自动复制,分区表结构的中间表已经生成。

4、检查中间表是否开启行迁移

  1. select row_movement from dba_tables where table_name='LUCIFER' and owner='PAR'
  2. select row_movement from dba_tables where table_name='LUCIFER_PAR' and owner='PAR'

5、收集表统计信息

为了确保数据准确,开始前进行统计信息收集:

  1. sqlplus / as sysdba 
  2. exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ; 
  3. exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER_PAR',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ; 

6、开始在线重定义

  1. sqlplus / as sysdba 
  2. EXEC DBMS_REDEFINITION.START_REDEF_TABLE('PAR','LUCIFER','LUCIFER_PAR'); 

7、复制表属性,排除索引

选择自动复制表属性,手动创建本地索引(local):

  • 优点:只需要关注索引是否遗漏,无需关注触发器,权限,约束等依赖。
  • 缺点:需要手动创建索引,并且结束后手动rename索引。
  1. sqlplus par/par 
  2. SET SERVEROUTPUT ON 
  3. DECLARE 
  4.   l_errors  NUMBER; 
  5. BEGIN 
  6.   DBMS_REDEFINITION.copy_table_dependents( 
  7.     uname            => USER
  8.     orig_table       => 'LUCIFER'
  9.     int_table        => 'LUCIFER_PAR'
  10.     copy_indexes     => 0, 
  11.     copy_triggers    => TRUE
  12.     copy_constraints => TRUE
  13.     copy_privileges  => TRUE
  14.     ignore_errors    => FALSE
  15.     num_errors       => l_errors, 
  16.     copy_statistics  => FALSE
  17.     copy_mvlog       => FALSE); 
  18.      
  19.   DBMS_OUTPUT.put_line('Errors=' || l_errors); 
  20. END

执行过程没有任何报错,代表正常。

8、中间表创建本地索引

中间表LUCIFER_PAR创建索引:

  1. create index ID_NAME_PAR on LUCIFER_PAR(NAME) tablespace PAR local parallel 8; 

注意:索引名称需要与原索引名称不一致。

9、取消索引并行度

如果创建索引时,开启并行创建,则需要取消索引并行度:

  1. sqlplus / as sysdba 
  2. select 'alter index '||owner||'.'||index_name||' noparallel;' 
  3. from dba_indexes  
  4. where table_name = 'LUCIFER_PAR' and owner= 'PAR'

10、同步数据(可以减少结束重定义过程的锁表时间)

  1. sqlplus / as sysdba 
  2. BEGIN 
  3. dbms_redefinition.sync_interim_table( 
  4. uname => 'PAR'
  5. orig_table => 'LUCIFER'
  6. int_table => 'LUCIFER_PAR'); 
  7. END

注意: 这一步操作是为了在结束重定义的时候,减少锁表的时间。

11、收集中间表统计信息

为了下面同步数据做准备,收集中间表统计信息:

  1. sqlplus / as sysdba 
  2. exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER_PAR',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ; 

12、结束重定义(结束重定义需要锁表,具体时间根据表的大小决定)

  1. sqlplus / as sysdba 
  2. BEGIN 
  3. dbms_redefinition.finish_redef_table( 
  4. uname => 'PAR'
  5. orig_table => 'LUCIFER'
  6. int_table => 'LUCIFER_PAR'); 
  7. END

13、查看分区表是否已转换

  1. sqlplus par/par 
  2. select owner,table_name,partitioned from user_tables where table_name in ('LUCIFER','LUCIFER_PAR'); 

如上,LUCIFER表已经在线重定义为分区表结构。

14、手动修改重命名索引

此时,原表名的表已经转换为中间表,需要先将原表的索引,rename到其他名字,本次是BAK,需要注意索引名称长度不能过长

  1. sqlplus / as sysdba 
  2. ALTER index PAR.ID_NAME RENAME TO ID_NAME_BAK; 

rename新分区表索引,由于新分区表的索引名称还是中间表的索引名称,所以需要手动rename:

  1. sqlplus / as sysdba 
  2. ALTER index PAR.ID_NAME_PAR RENAME TO ID_NAME; 

15、查看是否存在无效索引

  1. sqlplus / as sysdba 
  2. SELECT owner index_owner, index_name, index_type,'N/A' partition_name,status,table_name,tablespace_name, 
  3.   'alter index '||owner||'.'||index_name||' rebuild;' rebuild_index 
  4.   FROM dba_indexes 
  5. WHERE status = 'UNUSABLE' 
  6. UNION ALL 
  7. SELECT a.index_owner,a.index_name,b.index_type,a.partition_name,a.status,b.table_name,a.tablespace_name, 
  8. 'alter index '||a.index_owner||'.'||a.index_name||' rebuild partition '||a.partition_name||' ;' rebuild_index 
  9.   FROM dba_ind_partitions a, dba_indexes b 
  10. WHERE a.index_name = b.index_name 
  11.    AND a.index_owner = b.owner 
  12.    AND a.status = 'UNUSABLE' 
  13. UNION ALL 
  14. SELECT owner index_owner,a.index_name,b.index_type,'N/A' partition_name,a.status,b.table_name,NULL
  15. 'alter index '||a.index_owner||'.'||a.index_name||' rebuild subpartition '||a.subpartition_name||';' rebuild_index 
  16.   FROM dba_ind_subpartitions a, dba_indexes b 
  17. WHERE a.index_name = b.index_name 
  18.    AND a.index_owner = b.owner 
  19.    AND a.status = 'UNUSABLE'

16、检查切换后是否开启row_movement

  1. sqlplus / as sysdba 
  2. select owner,table_name,row_movement from dba_tables where table_name in ('LUCIFER','LUCIFER_PAR'and owner='PAR'

17、检查无效对象

  1. ##无效对象编译 
  2. sqlplus / as sysdba  
  3. @?/rdbms/admin/utlrp.sql 
  4.  
  5. select  'alter  '||object_type||'   '||owner||'.'||object_name||'   compile;' 
  6. from  dba_objects t 
  7. where t.status = 'INVALID' order by 1; 

 

18、收集统计信息

  1. sqlplus / as sysdba 
  2. exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ; 

19、插入测试数据

  1. sqlplus par/par 
  2. begin 
  3.   for i in 100 .. 124 loop 
  4.     insert into lucifer values 
  5.       (i, 
  6.        'lcuifer_' || i, 
  7.        add_months(to_date('2021-5-1''yyyy-mm-dd'), i)); 
  8.   end loop; 
  9.   commit
  10. end

20、查询分区表数据分布

  1. sqlplus par/par 
  2. SELECT COUNT(*) FROM  LUCIFER; 
  3. SELECT * FROM  LUCIFER PARTITION(LUCIFER_P202101); 
  4. SELECT * FROM  LUCIFER PARTITION(LUCIFER_P202201); 
  5. SELECT * FROM  LUCIFER PARTITION(LUCIFER_MAX); 

可以发现,数据已经根据日期均匀分布在不同的子分区中。

至此,在线重定义已经完成,分区表已成功转换。

「参考MOS文档:」

  • How To Partition Existing Table Using DBMS_REDEFINITION (Doc ID 472449.1)

 

责任编辑:姜华 来源: Lucifer三思而后行

同话题下的热门内容

生产环境Oracle undo表空间管理的优秀实践使用 Convert Database 命令进行 RMAN 跨平台迁移(12C至19C)AutoUpgrade 快速升级 Oracle 数据库

编辑推荐

Oracle免费的便捷Web应用开发框架二则一次去重 80w 数据时夯死临时处理实例如何只授予用户查看存储过程定义的权限通过Oracle来辅助MySQL数据问题的恢复是否值得付费?Oracle,Open JDK等四大JVM性能全面对比
我收藏的内容
点赞
收藏