在线重定义与普通表改为分区表

ORACLE分区表梳理系列（一）- 分区表概述、分类、使用方法及注意事项 https://www.cnblogs.com/yumiko/p/6095036.html ORACLE分区表梳理系列（二）- 分区表日常维护及注意事项（红字需要留意） https://www.cnblogs.com/yumiko/p/6163523.html 一、什么是在线重定义 要了解什么是在线重定义技术，我想从表分区开始说起。在生产系统运维过程中，经常遇到的一个需求是如何把一个数据量非常大的普通表改造成分区表。分区最早在oracle8.0版本引入，支持将一个表或索引物理地分解为多个更小、更可管理的部分。 以下是在线重定义表的一些功能： 1.修改表的存储参数； 2.可以将表转移到其他表空间； 3.增加并行查询选项； 4.增加或删除分区； 5.重建表以减少碎片； 6.将堆表改为索引组织表或相反的操作； 7.增加或删除一个列。   在线重定义好处： - 提高数据的可用性 - 数据段变得更小，减轻了管理的负担 - 改善某些查询的性能 - 将数据修改分布到多个单独的分区上，减少竞争 分区表在各行业的数据库都得到广泛应用，但是有些业务系统在设计阶段对系统数据和性能容量增长估计不足，或没有考虑到运维过程中的数据归档需求，往往没有对表做分区设计。在生产运行经过长时间的数据积累之后，才发现表越来越大，某些查询或插入数据的性能变得越来越慢，迫切需要做表分区改造。 那么问题来了，业务系统往往都是7*24在线作业，改造的过程又必然涉及表结构的变动，如果对表进行重建，会对系统运行产生非常大的影响，通常会设置计划停机窗口来做这类维护操作。 当然，分区表的改造只是诸多数据重组织或重定义场景中的一种，在数据变动需求越来越多、越来越复杂，而系统停机的成本又显著升高的背景下，从Oracle 8i开始就设计了有限的在线重新组织数据的功能，例如create indexes online, rebuilding indexes online。并在9i进一步扩展这方面的能力，引入了数据在线重定义。 在线重定义技术允许数据库管理员在该表上有读写数据操作的情况下，非常灵活地修改表的物理属性、表数据、表结构。 二、在线重定义的使用场景 有以下变更需求时，都可以考虑使用在线重定义技术，这些场景也是运维过程中经常遇到的：  - 修改表的物理属性、存储参数  - 将表迁移到别的表空间  - 消除表碎片、释放空间  - 在表中增加、删除或重命名字段  - 大批量改变表中的数据   三、在线重定义的实现原理 oracle提供了一个dbms_redefinition包用于在线重定义操作，主要包含如下三个过程： dbms_redefinition.start_redef_table  这个过程首先会创建一个快速刷新的物化视图作为过渡表，然后将源表的数据加载到过渡表中，并在源表上创建物化视图日志，以支持快速刷新同步数据。 dbms_redefinition.sync_interim_table 用来把源表中的数据同步到过渡表。 dbms_redefinition.finish_redef_table 这个过程的操作步骤比较多，也是做在线重定义时需要特别注意的，但其执行时间通常是非常短的： 1）先调用一次dbms_redefinition.sync_interim_table，同步数据。 2）锁定源表，锁定之后表数据不再允许发生变化。 3）再调用一次dbms_redefinition.sync_interim_table，同步数据。 4）交换源表和过渡表的表名。 5）删除物化视图和物化视图日志。 6）释放表锁资源。 四、实验将普通表改造成分区表 下面我们通过实际案例来应用这项技术，本次实践中我们要弄清楚几个问题： a. 在线重定义的操作过程。 b. 将一个2000万数据量的表进行重定义，需要多长时间。 c. 在线重定义期间，表相关的操作是否受影响，又是如何影响的。 1. 检查用户权限 运行dbms_redefinition包需要以下权限：  - execute privilege to dbms_redefinition  - create any table  - alter any table  - drop any table  - lock any table  - select any table  - create any index  - create any trigger grant execute on dbms_redefinition to SCOTT;  grant create any table to SCOTT;   grant alter any table to SCOTT;    grant drop any table to SCOTT;     grant lock any table to SCOTT;     grant select any table to SCOTT;   grant create any index to SCOTT;   grant create any trigger to SCOTT; 可进入用户后执行以下SQL进行检查确认： select * from session_privs; 2.实验创建一个源表，并插入数据 create table unpar_table(id number(10),create_date date,name varchar2(100),up_date date); --使用批量绑定技术插入200万数据初始测试数据 declare   type t_mid is table of unpar_table%rowtype index by binary_integer;   l_tab_mid t_mid; begin   for i in 1 .. 2000000 loop     l_tab_mid(i).id := i;     l_tab_mid(i).create_date := sysdate;     l_tab_mid(i).name := lpad('a', 100, 'a');     l_tab_mid(i).up_date := sysdate;   end loop;   forall i in 1 .. l_tab_mid.count     insert into unpar_table values l_tab_mid (i);   commit; end; / --给表unpar_table增加主键约束及建索引 alter table unpar_table add (constraint unpar_table_pk primary key (id)); create index create_date_ind on unpar_table(create_date); 注意：在线重定义方法。存在两种重定义方法，一种是基于主键、另一种是基于ROWID。ROWID的方式不能用于索引组织表，而且重定义后会存在隐藏列M_ROW$$。默认采用主键的方式。(重定义的表如果基于主键，则必须要有主键才能进行重定义) --收集统计信息 exec dbms_stats.gather_table_stats(ownname => 'SCOTT',TABNAME => 'UNPAR_TABLE',cascade => true); 3.按需求创建一个已分区的中间表 create table par_table(id number(10),create_date date,name varchar2(100),up_date date) partition by range(create_date) ( partition unpar_table_1 values less than (to_date('01/01/2012','DD/MM/YYYY')), partition unpar_table_2 values less than (to_date('01/01/2013','DD/MM/YYYY')), partition unpar_table_3 values less than (to_date('01/01/2014','DD/MM/YYYY')), partition unpar_table_4 values less than (to_date('01/01/2015','DD/MM/YYYY')), partition unpar_table_5 values less than (to_date('01/01/2016','DD/MM/YYYY')), partition unpar_table_6 values less than (to_date('01/01/2017','DD/MM/YYYY')), partition unpar_table_7 values less than (maxvalue) ); 以上步骤完成准备工作，开始执行在线重定义过程。 4.检查源表是否具备在线重定义的条件 exec dbms_redefinition.can_redef_table('SCOTT','UNPAR_TABLE'); --检查耗时 SQL> exec dbms_redefinition.can_redef_table('SCOTT','UNPAR_TABLE'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.17 5.开始在线重定义，这一步相当于初始化工作，耗时比较长 exec dbms_redefinition.start_redef_table('SCOTT','UNPAR_TABLE','PAR_TABLE'); --检查耗时 SQL> exec dbms_redefinition.start_redef_table('SCOTT','UNPAR_TABLE','PAR_TABLE'); PL/SQL procedure successfully completed. Elapsed: 00:00:43.29 6.在中间表上创建约束和索引并收集统计信息 这一步提前做，可以防止重定义完成后，新表没有可用索引，而产生性能问题。 oracle提供了dbms_redefinition.copy_table_dependents过程，用于复制源表上的索引、约束、触发器、权限等依赖关系到中间表，但是这个包存在的BUG也不少，可以选择性使用。 alter table par_table add (constraint unpar_table_pk2 primary key (id)); --耗时:Elapsed: 00:00:08.93 create index create_date_ind2 on par_table(create_date); --耗时:Elapsed: 00:00:10.07 exec dbms_stats.gather_table_stats(ownname => 'SCOTT',TABNAME => 'PAR_TABLE',cascade => true); --耗时:Elapsed: 00:00:02.89 注意： 如果在执行DBMS_REDEFINITION.START_REDEF_TABLE()过程和执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程直接在重定义表上执行了大量的DML操作，那么可以选择执行一次或多次的SYNC_INTERIM_TABLE()过程，以减少最后一步执行FINISH_REDEF_TABLE()过程时的锁定时间。 7.手工同步数据，将上一步执行中将产生的数据先做同步刷新 exec dbms_redefinition.sync_interim_table('SCOTT','UNPAR_TABLE','PAR_TABLE');  --检查耗时 SQL> exec dbms_redefinition.sync_interim_table('SCOTT','UNPAR_TABLE','PAR_TABLE');  PL/SQL procedure successfully completed. Elapsed: 00:00:00.22 8.完成在线重定义过程：执行后，中间表和源表的表名互换 exec dbms_redefinition.finish_redef_table('SCOTT','UNPAR_TABLE','PAR_TABLE');  --检查耗时 SQL> exec dbms_redefinition.finish_redef_table('SCOTT','UNPAR_TABLE','PAR_TABLE');  PL/SQL procedure successfully completed. Elapsed: 00:00:01.64 9.删除中间表，并将索引重命名回来 此时的中间表已经是原来未分区的普通表，而源表已经变成了分区表 --先检查分区表及普通表情况 select table_name,partition_name,num_rows from user_tab_partitions where table_name like '%PAR_TABLE%'; select a.segment_name,a.segment_type,sum(a.bytes/1024/1024) size_m from user_segments a where a.segment_name like '%PAR_TABLE%' group by a.segment_name,a.segment_type; select table_name,index_name,status from user_indexes where table_name like '%PAR_TABLE%';  SQL> select table_name,partition_name,num_rows from user_tab_partitions where table_name like '%PAR_TABLE%'; TABLE_NAME                     PARTITION_NAME                   NUM_ROWS ------------------------------ ------------------------------ ---------- UNPAR_TABLE                    UNPAR_TABLE_7                     2000000 UNPAR_TABLE                    UNPAR_TABLE_6                           0 UNPAR_TABLE                    UNPAR_TABLE_5                           0 UNPAR_TABLE                    UNPAR_TABLE_4                           0 UNPAR_TABLE                    UNPAR_TABLE_3                           0 UNPAR_TABLE                    UNPAR_TABLE_2                           0 UNPAR_TABLE                    UNPAR_TABLE_1                           0 7 rows selected. SQL> select a.segment_name,a.segment_type,sum(a.bytes/1024/1024) size_m from user_segments a where a.segment_name like '%PAR_TABLE%' group by a.segment_name,a.segment_type; SEGMENT_NAME    SEGMENT_TYPE           SIZE_M --------------- ------------------ ---------- UNPAR_TABLE     TABLE PARTITION           288 PAR_TABLE       TABLE                     280 UNPAR_TABLE_PK  INDEX                      35 UNPAR_TABLE_PK2 INDEX                      44 SQL> select table_name,index_name,status from user_indexes where table_name like '%PAR_TABLE%';  TABLE_NAME                     INDEX_NAME                     STATUS ------------------------------ ------------------------------ -------- UNPAR_TABLE                    CREATE_DATE_IND2               VALID UNPAR_TABLE                    UNPAR_TABLE_PK2                VALID PAR_TABLE                      CREATE_DATE_IND                VALID PAR_TABLE                      UNPAR_TABLE_PK                 VALID --操作 drop table par_table purge; alter table unpar_table rename constraint unpar_table_pk2 to unpar_table_pk; alter index unpar_table_pk2 rename to unpar_table_pk; alter index create_date_ind2 rename to create_date_ind; --验证查询 SQL> select table_name,index_name,status from user_indexes where table_name like '%PAR_TABLE%';  TABLE_NAME                     INDEX_NAME                     STATUS ------------------------------ ------------------------------ -------- UNPAR_TABLE                    CREATE_DATE_IND                VALID UNPAR_TABLE                    UNPAR_TABLE_PK                 VALID SQL> select a.segment_name,a.segment_type,sum(a.bytes/1024/1024) size_m from user_segments a where a.segment_name like '%PAR_TABLE%' group by a.segment_name,a.segment_type; SEGMENT_NAME    SEGMENT_TYPE           SIZE_M --------------- ------------------ ---------- UNPAR_TABLE     TABLE PARTITION           288 UNPAR_TABLE_PK  INDEX                      44 至此，使用在线重定义进行表分区改造的工作已经完成。 五、在线重定义需注意的问题 使用在线重定义技术，以下情况是需要注意的：  - 如果离线操作能够解决问题，就不要用在线重定义例如一些静态数据、历史数据的归档迁移，可使用CTAS、alter table move、或导出导入完成  - 表空间至少要留有比源表所用空间更大的剩余空间  - 在线重定义的操作过程耗时较长，但对业务的影响最小  - 要注意源表上的事务操作，如果过于频繁，可能会发生较严重的等待