服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Oracle - Oracle 分区表之在线重定义

Oracle 分区表之在线重定义

2023-05-07 06:11未知服务器之家 Oracle

一、介绍 「DBMS_REDEFINITION(在线重定义)：」 「支持的数据库版本」：Oracle Database - Enterprise Edition - Version 9.2.0.4 and later 在线重定义是通过 「物化视图」 实现的。 「使用在线重定义的一些限制条件」： 必须有足够的表空间来容纳表

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; 

Oracle 分区表之在线重定义

创建测试表：

  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; 

 Oracle 分区表之在线重定义

插入测试数据：

  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; 
  11. / 

Oracle 分区表之在线重定义

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

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'; 

Oracle 分区表之在线重定义

确认是否可以重定义，没有主键用 rowid：

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

Oracle 分区表之在线重定义

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

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; 
  8. / 

Oracle 分区表之在线重定义

创建中间分区表 lucifer_par：

  1. create table lucifer_par 
  2. ( 
  3.   id       NUMBER(8), 
  4.   name     VARCHAR2(20), 
  5.   par_date DATE 
  6. ) 
  7. partition BY RANGE(par_date)( 
  8. 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, 
  9. 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, 
  10. 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, 
  11. 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, 
  12. 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, 
  13. 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, 
  14. 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, 
  15. 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, 
  16. 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, 
  17. 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, 
  18. 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, 
  19. 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, 
  20. 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, 
  21. 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, 
  22. 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, 
  23. 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, 
  24. 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, 
  25. 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, 
  26. 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, 
  27. 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, 
  28. 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, 
  29. 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, 
  30. 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, 
  31. 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, 
  32. partition lucifer_MAX values less than (maxvalue) tablespace par) 
  33.              enable row movement 
  34.              tablespace par; 

Oracle 分区表之在线重定义

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

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'; 

Oracle 分区表之在线重定义

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') ; 

Oracle 分区表之在线重定义

6、开始在线重定义

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

Oracle 分区表之在线重定义

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; 
  21. / 

Oracle 分区表之在线重定义

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

8、中间表创建本地索引

中间表LUCIFER_PAR创建索引：

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

Oracle 分区表之在线重定义

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

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'; 

Oracle 分区表之在线重定义

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; 
  8. / 

Oracle 分区表之在线重定义

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

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') ; 

Oracle 分区表之在线重定义

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; 
  8. / 

Oracle 分区表之在线重定义

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

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

Oracle 分区表之在线重定义

Oracle 分区表之在线重定义

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

14、手动修改重命名索引

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

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

Oracle 分区表之在线重定义

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

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

Oracle 分区表之在线重定义

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'; 

Oracle 分区表之在线重定义

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'; 

Oracle 分区表之在线重定义

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; 

 Oracle 分区表之在线重定义

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') ; 

Oracle 分区表之在线重定义

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; 
  11. / 

Oracle 分区表之在线重定义

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); 

Oracle 分区表之在线重定义

Oracle 分区表之在线重定义

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

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

「参考MOS文档：」

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

 

延伸 · 阅读

精彩推荐
最近更新
编辑推荐
阅读排行
热门标签
1033 　 1455 　 12154 　 10231 　 12514 　 54013 　 数据库系统 　 快照 　 分区策略 　 数据库集 　 异常关闭 　 Oracle9iPL 　 SQL编程 　 日期函数 　 SQL性能优化 　 并行查询 　 紧急故障 　 完全卸载 　 Win2k 　 IMP 　 分区管理 　 密码文件 　 AutoUpgrade 　 异地备份 　 交叉表 　 加载数据库错误 　 areasQueries 　 逻辑备份 　 SH文件 　 数据文件 　
© 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图