利用Oracle在线重定义Online Redefinition清理历史数据

我在<了解Oracle在线重定义Online Redefinition>一文中介绍了Oracle在线重定义的特点及其使用步骤,Online Redefinition的适用场景很多,包括:

 

  • Modify the storage parameters of a table or cluster
  • Move a table or cluster to a different tablespace
  • Add, modify, or drop one or more columns in a table or cluster
  • Add or drop partitioning support (non-clustered tables only)
  • Change partition structure
  • Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
  • Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table
  • Add support for parallel queries
  • Re-create a table or cluster to reduce fragmentation
  • Change the organization of a normal table (heap organized) to an index-organized table, or do the reverse.
  • Convert a relational table into a table with object columns, or do the reverse.
  • Convert an object table into a relational table or a table with object columns, or do the reverse.

 

但是Online Redefinition恰恰就是不支持对于表上历史数据的清理( 甚至于数据更新update都是支持的), 对于存有海量数据且没有分区的大表而言历史数据的清理是非常头痛的工作,特别是在7*24 应用不能下线的环境当中, 我们往往无法利用CTAS或INSERT APPEND+NOLOGGING的方式重建表以加速清理工作,而仅能使用最为原始的DELETE DML, 而我们知道DELETE操作是很缓慢的(与之前所提及的方法相比,真实世界中DELETE的效率还会受到INDEX clustering_factor聚集因子等因素的影响而显得更慢,见<SQL调优:Clustering Factor影响数据删除速度一例>), 且为了避免ORA-01555快照过久的错误出现,我们不能简单地使用一条DELETE SQL来清理数据,而需要使用PL/SQL匿名块控制并定期commit提交以避免ORA-01555。

 

实际上我们还是可以通过将非分区表Online Redefinition转换为以删除条件为Range范围分区的Partition-ed Table,再直接Truncate Partiton的方法来加速历史数据的清理, 同时又不影响业务的在线。

 

例如有如下非分区表一张:

 

create table order_history
(
order_id number primary key,
issue_date date ,
location varchar2(200),
amount number,
maclean varchar2(200),
QUANTITY_SOLD number,
PROMO_ID number,
CUST_ID number,
CHANNEL_ID number) tablespace users pctfree 0;

SQL> select count(*) from ORDER_HISTORY;

  COUNT(*)
----------
   4550092

SQL> select count(*) from ORDER_HISTORY where issue_date< sysdate-365;

  COUNT(*)
----------
   3412569

SQL> exec dbms_stats.gather_table_stats('SH','ORDER_HISTORY');

PL/SQL procedure successfully completed.

 

该表上存有450万条Order记录 , 其中340万条是一年之前的历史记录需要予以删除。

我们先着手将该非分区表在线冲定义为以issue_date日期为范围的Range Partition。

 

以下我们会创建临时分区表,并完成Online Redefinition:

 

create table order_history_int
(
order_id number primary key,
issue_date date ,
location varchar2(200),
amount number,
maclean varchar2(200),
QUANTITY_SOLD number,
PROMO_ID number,
CUST_ID number,
CHANNEL_ID number)
partition by range(issue_date)
( partition p1 values less than (to_date('2009-01-01','YYYY-MM-DD')) tablespace users,
  partition p2 values less than (to_date('2010-01-01','YYYY-MM-DD')) tablespace users,
  partition p3 values less than (to_date('2011-01-01','YYYY-MM-DD')) tablespace users,
  partition p4 values less than (to_date('2012-01-01','YYYY-MM-DD')) tablespace users,
  partition p5 values less than (maxvalue) tablespace users);

set timing on;

SQL> begin
  2    dbms_redefinition.can_redef_table(uname        => 'SH',
  3                                      tname        => 'ORDER_HISTORY',
  4                                      options_flag => DBMS_REDEFINITION.cons_use_pk);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10

这里采用了DBMS_REDEFINITION.cons_use_pk, 即Primary Key的重定义方式
若没有主键和伪主键,那么也可以选择rowid的方式,当然我们更推荐使用主键

SQL> begin
  2    DBMS_REDEFINITION.START_REDEF_TABLE(uname        => 'SH',
  3                                        orig_table   => 'ORDER_HISTORY',
  4                                        int_table    => 'ORDER_HISTORY_INT',
  5                                        options_flag => DBMS_REDEFINITION.cons_use_pk);
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:25.10 

SQL> select count(*) from ORDER_HISTORY_INT;

  COUNT(*)
----------
   4550092

Elapsed: 00:00:00.24

SQL> DECLARE
  2    num_errors PLS_INTEGER;
  3  BEGIN
  4    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname            => 'SH',
  5                                            orig_table       => 'ORDER_HISTORY',
  6                                            int_table        => 'ORDER_HISTORY_INT',
  7                                            copy_indexes     => 0,
  8                                            copy_triggers    => TRUE,
  9                                            copy_constraints => FALSE,
 10                                            copy_privileges  => TRUE,
 11                                            ignore_errors    => FALSE,
 12                                            num_errors       => num_errors,
 13                                            copy_statistics  => TRUE);
 14  END;
 15  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.82

SQL> begin
  2    dbms_redefinition.finish_redef_table(uname      => 'SH',
  3                                         orig_table => 'ORDER_HISTORY',
  4                                         int_table  => 'ORDER_HISTORY_INT');
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.87

 

以上仅用了不到2分钟时间就完成了对ORDER_HISTORY历史表的在线重定义,紧接着我们只需要truncate前几年的历史分区即可,因为truncate操作是DDL语句且与DELETE的机制大相径庭,所以速度一般是很快的:

但是请注意若表上有global Index,那么这样可能会导致索引失效,所以建议在Online Redefinition时将全局索引转换为本地索引,或者使用update global indexes;子句

 

 

SQL>  alter table ORDER_HISTORY truncate partition p1 [update global indexes];

Table truncated.

Elapsed: 00:00:00.21

SQL> alter table ORDER_HISTORY truncate partition p2 [update global indexes];

Table truncated.

Elapsed: 00:00:00.07

SQL> alter table ORDER_HISTORY truncate partition p3 [update global indexes];

Table truncated.

Elapsed: 00:00:00.07

SQL> select count(*) from ORDER_HISTORY;

  COUNT(*)
----------
   1137523

Elapsed: 00:00:00.14

SQL> select count(*) from ORDER_HISTORY where issue_date< sysdate-365;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.02

 

 

以上我们通过Oracle在线重定义Online Redefinition特性将非分区的历史数据表在线转化为以日期为范围的分区表,利用分区表truncate partition清理数据的便利性来解决传统以PL/SQL过程游标获取rowid循环方式删除历史数据十分缓慢的问题,同时也保证了业务应用的始终在线。

Slide:了解Oracle在线重定义online redefinition

Slideshare版的了解Oracle在线重定义online redefinition,可供下载,下载地址。

 

了解Oracle在线重定义Online Redefinition

Online Redefinition在线重定义对象是Oracle中很酷的一种特性,它可以帮助我们在7*24在线的系统中从容地做出数据对象的在线定义修改,是Oracle数据库保证其高可用性的重要技术。

 

 

 

在线重定义Online Redefinition特性在许多场景中都是十分有用的,例如:

  •     修改表的Storage存储参数
  •     在同一Schema下将表移动到不同的表空间
  •     转换非分区表为分区表
  •     添加或删除分区
  •     重新创建表以减少碎片,降低高水位
  •     将堆组织的表改变为索引组织表
  •     添加或删除列

 

使用Online Redefinition在线重定义需要用到DBMS_REDEFINITION程序包,EXECUTE_CATALOG_ROLE角色默认被赋予该PL/SQL Package的执行权限。除了执行该程序包的权限外,用户还需要拥有以下权限:

  •     CREATE ANY TABLE
  •     ALTER ANY TABLE
  •     DROP ANY TABLE
  •     LOCK ANY TABLE
  •     SELECT ANY TABLE

 

若要执行COPY_TABLE_DEPENDENTS存储过程则还需要以下权限:

  •     CREATE ANY TRIGGER
  •     CREATE ANY INDEX

 

 

在早期版本中在线重定义(Online Redefinition)对于某些具有特殊数据类型的表存在诸多限制,从Oracle 10g开始拥有以下数据类型的表也支持在线重定义(Online Redefinition)了:

 

  1. 存在LONG类型column字段的表可以被在线重定义(redefined online);但是LONG类型字段只能被转换为character large object即CLOB
  2. 存在LONG RAW类型column字段的表可以被在线重定义(redefined online);但是LONG RAW类型字段只能被转换为binary large object 即BLOB

 

此外从10g开始支持对replication table的在线重定义:

  1. 包含在master-master replications中的表
  2. 在n-way master复制环境中没有horizontal或vertical subsetting,或者允许 column transformations的表

 

我们可以通过以下步骤一步一步地完成对一张普通堆表的在线重定义(Online Redefinition):

 

步骤1:决定我们所要使用的重定义方式(redefinition method),存在2种方式:

 

  • 第一种是我们较为推荐的方式,采用Primary Key主键或者pseudoprimary key伪主键实施重定义(从Oracle 10g开始支持pseudoprimary key伪主键)。 这里pseudoprimary key伪主键要求是唯一键且所有的成员列均是非空NOT NULL。使用该种方式,重定义前和重定义后版本的表均必须有相同的Primary Key主键或者pseudoprimary key伪主键列。不管是从性能角度,还是从操作的复杂度考虑,常规场景中都推荐尽可能使用此种方式
  • 第二种方式是使用rowid进行redefinition。首先索引组织表index-organized table (IOT)不支持使用rowid的重定义方式。此外,若使用该种redefinition方式,最终会有一个隐藏的字段M_ROW$$被加入到重定义后版本的表上,Oracle官方推荐在重定义完成后将该M_ROW$$字段drop掉或者标记为unused。

 

步骤2:通过调用DBMS_REDEFINITION.CAN_REDEF_TABLE存储过程并以OPTIONS_FLAG(flag indicating user options to use)参数指定所要使用的重定义方式,来验证原表是否可以以这样的方式来在线重定义。若原表不符合指定重定义方式的要求,那么该过程会报出一个错误,说明该表不能在线重定义的具体原因。

若指定OPTIONS_FLAG为常数DBMS_REDEFINITION.CONS_USE_PK(cons_use_pk    CONSTANT PLS_INTEGER := 1;),意为使用primary keys 或 pseudoprimary keys的重定义方式。

若指定OPTIONS_FLAG为常数DBMS_REDEFINITION.CONS_USE_ROWID(cons_use_rowid CONSTANT PLS_INTEGER := 2;),意为使用rowid的重定义方式。

DBMS_REDEFINITION.CAN_REDEF_TABLE过程的详细定义如下:

 

  -- NAME:     can_redef_table - check if given table can be re-defined
  -- INPUTS:   uname        - table owner name
  --           tname        - table name
  --           options_flag - flag indicating user options to use
  --           part_name    - partition name
  PROCEDURE can_redef_table(uname        IN VARCHAR2,
                            tname        IN VARCHAR2,
                            options_flag IN PLS_INTEGER := 1,
                            part_name    IN VARCHAR2 := NULL);

 

 

步骤3:在原表的同一Schema下创建一张空的临时表(interim table), 该表具有所有我们想要的属性。若有column字段需要通过重定义drop掉,那么就在这张临时表的定义中去掉该column。同理若有column字段需要通过重定义加入到表上,那么就在临时表上加入该column的定义。

理论上我们可以并行地实施表的在线重定义;若已经同时指定了原表和临时表的并发度,那么也请确保实施操作的本会话(session)已经启用了会话级别的并行执行, 这样Oracle服务进程会在实施重定义的过程中尽可能地使用并行执行( parallel execution )。

 

可以采用以下ALTER SESSION命令启用并行的DML和查询:

 

alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;

 

如原表的结构为HR.JOBS:

 

SQL> desc hr.jobs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB_ID                                    NOT NULL VARCHAR2(10)
 JOB_TITLE                                 NOT NULL VARCHAR2(35)
 MIN_SALARY                                         NUMBER(8)
 MAX_SALARY                                         NUMBER(8)
 EXEMPT_STATUS                                      VARCHAR2(3)

 

希望在此原表结构基础上加入默认为188的Number类型名为Maclean的字段column,那么我们创建临时表如下:

 

SQL>  create table   hr.jobs_hist_int
  2   ( job_id varchar2(10) primary key,
  3     job_title varchar2(35) NOT NULL,
  4     min_salary number(8),
  5     max_salary number(8),
  6     exempt_status varchar2(3),
  7     maclean number(8) default 188);

Table created.

 

步骤4: 调用DBMS_REDEFINITION.START_REDEF_TABLE存储过程启动重定义进程,使用该过程需要指定以下的参数:

  1. 将要重定义的表名
  2. 临时表的名字(interim table name)
  3. 字段的映射信息(column mapping)
  4. 重定义使用的方式(primary key or rowid)
  5. 此外还可以指定用以排序的字段

 

若字段映射参数column mapping未指定,那么Oracle假设所有原表上的列(字段名不变)都被包含在中间表上了。 若指定了column mapping信息,那么只有那些在字段映射中明确指定的原表字段被考虑重定义。若没有指定使用何种重定义方式,那么Oracle默认会采用primary keys或pseudoprimary keys的方式。

从10g开始出现了ORDERBY_COLS参数可以用于指定在临时表初始化过程中数据行按照字段排序。

 

DBMS_REDEFINITION.START_REDEF_TABLE过程的具体定义如下:

 

 -- NAME:     start_redef_table - start the online re-organization
  -- INPUTS:   uname        - schema name
  --           orig_table   - name of table to be re-organized
  --           int_table    - name of interim table
  --           col_mapping  - select list col mapping
  --           options_flag - flag indicating user options to use
  --           orderby_cols - comma separated list of order by columns
  --                          followed by the optional ascending/descending
  --                          keyword
  --           part_name    - name of the partition to be redefined
  PROCEDURE start_redef_table(uname        IN VARCHAR2,
                              orig_table   IN VARCHAR2,
                              int_table    IN VARCHAR2,
                              col_mapping  IN VARCHAR2 := NULL,
                              options_flag IN BINARY_INTEGER := 1,
                              orderby_cols IN VARCHAR2 := NULL,
                              part_name    IN VARCHAR2 := NULL);

 

步骤5: 使用10g以后出现的COPY_TABLE_DEPENDENTS存储过程在临时表上自动创建如constraints, triggers, indexes,privileges类型的依赖对象(dependent object)。该COPY_TABLE_DEPENDENTS过程同时也会注册这些依赖对象。

使用COPY_TABLE_DEPENDENTS克隆依赖对象要比后面介绍REGISTER_DEPENDENT_OBJECTS过程来得简单方便。

该存储过程的NUM_ERRORS(number of errors that occurred while cloning ddl)输出参数,显示了其运行过程中所产生的错误数量。若IGNORE_ERRORS(TRUE implies continue after errors, FALSE otherwise)参数设置为TRUE,那么该过程会忽略错误信息并不输出,继续其工作。若设置为FALSE,那么错误会在错误堆栈中显性输出。

 

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS过程的定义如下:

 

  --  NAME:     copy_table_dependents
  --
  --  INPUTS:  uname             - schema name
  --           orig_table        - name of table to be re-organized
  --           int_table         - name of interim table
  --           copy_indexes      - integer value indicating whether to
  --                               copy indexes
  --                               0 - don't copy
  --                               1 - copy using storage params/tablespace
  --                                   of original index
  --           copy_triggers      - TRUE implies copy triggers, FALSE otherwise
  --           copy_constraints   - TRUE implies copy constraints, FALSE
  --                                otherwise
  --           copy_privileges    - TRUE implies copy priviliges, FALSE
  --                                otherwise
  --           ignore errors      - TRUE implies continue after errors, FALSE
  --                                otherwise
  --           num_errors         - number of errors that occurred while
  --                                cloning ddl
  --           copy_statistics    - TURE implies copy table statistics, FALSE
  --                                otherwise.
  --                                If copy_indexes is 1, copy index
  --                                related statistics, 0 otherwise.
  PROCEDURE copy_table_dependents(uname              IN  VARCHAR2,
                                  orig_table         IN  VARCHAR2,
                                  int_table          IN  VARCHAR2,
                                  copy_indexes       IN  PLS_INTEGER := 1,
                                  copy_triggers      IN  BOOLEAN := TRUE,
                                  copy_constraints   IN  BOOLEAN := TRUE,
                                  copy_privileges    IN  BOOLEAN := TRUE,
                                  ignore_errors      IN  BOOLEAN := FALSE,
                                  num_errors         OUT PLS_INTEGER,
                                  copy_statistics    IN  BOOLEAN := FALSE);

 

我们可以通过查询10g以后出现的DBA_REDEFINITION_ERRORS视图(DBA_REDEFINITION_ERRORS is an online redefinition view and displays the dependent objects for which errors were raised while attempting to create similar objects on the interim table of the redefinition.)来判断在使用COPY_TABLE_DEPENDENTS存储过程克隆依赖对象过程中是否产生了错误。该视图记录了重定义过程中在克隆依赖对象时产生的错误。 克隆对象可能因缺少系统资源或原表的一个逻辑结构变化而失败。

 

在我们成功克隆这些依赖对象后,相关错误将会从该视图中被移除。 我们可以反复执行COPY_TABLE_DEPENDENTS或后面介绍的REGISTER_DEPENDENT_OBJECTS过程尝试重新克隆依赖对象。

 

示例错误如下:

 

SQL> select * from DBA_REDEFINITION_ERRORS;

OBJECT_TYP OBJECT_OWNER                   OBJECT_NAME
---------- ------------------------------ ------------------------------
BASE_TABLE_OWNER               BASE_TABLE_NAME
------------------------------ ------------------------------
DDL_TXT
--------------------------------------------------------------------------------
INDEX      HR                             JOB_ID_PK
HR                             JOBS
CREATE UNIQUE INDEX "HR"."TMP$$_JOB_ID_PK0" ON "HR"."INT_JOBS_HIST" ("JOB_ID")

 

步骤6: 这不是必需的步骤。我们也可以使用10g以后出现的REGISTER_DEPENDENT_OBJECT将正要重定义的表上的依赖对象注册到临时表上对应的依赖对象。 换句话说COPY_TABLE_DEPENDENTS的功能,REGISTER_DEPENDENT_OBJECT也是可以做到的,但是没有COPY_TABLE_DEPENDENTS来得简单方便。若我们想在原表的基础上建立额外的依赖对象,那么也可以用该过程来手动建立。若之前的COPY_TABLE_DEPENDENTS运行失败了,那么也可以通过REGISTER_DEPENDENT_OBJECT来手工补救,注册那些没有克隆成功的依赖对象。

注意REGISTER_DEPENDENT_OBJECT过程也是10g以后出现的,在早期版本中我们是要手动重命名依赖对象的。

 

DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT的详细定义如下:

 

  -- NAME:     register_dependent_object - register dependent object
  --
  -- INPUTS:   uname        - schema name
  --           orig_table   - name of table to be re-organized
  --           int_table    - name of interim table
  --           dep_type     - type of the dependent object
  --           dep_owner    - name of the dependent object owner
  --           dep_orig_name- name of the dependent object defined on table
  --                          being re-organized
  --           dep_int_name - name of the corressponding dependent object on
  --                          the interim table
  PROCEDURE register_dependent_object(uname         IN VARCHAR2,
                                      orig_table    IN VARCHAR2,
                                      int_table     IN VARCHAR2,
                                      dep_type      IN PLS_INTEGER,
                                      dep_owner     IN VARCHAR2,
                                      dep_orig_name IN VARCHAR2,
                                      dep_int_name  IN VARCHAR2);

 

与REGISTER_DEPENDENT_OBJECT相反, unregister_dependent_object过程用以注销依赖对象(unregister dependent object)。

通过查询10g以后出现的DBA_REDEFINITION_OBJECTS(an online redefinition view and displays the objects involved in the current redefinitions.)可以确认是否所有需要的依赖的对象都已被注册。该视图记录显示地被REGISTER_DEPENDENT_OBJECT注册的或隐式地被COPY_TABLE_DEPENDENTS注册的依赖对象。注意该视图仅包含当前重定义的信息。

 

步骤7:执行DBMS_REDEFINITION.FINISH_REDEF_TABLE  存储过程完成表的在线重定义。在此procedure运行过程中,原表会被以Exclusive lock mode(TM lmode=6)排他模式锁住极为短暂的一段时间(秒级),具体这段时间的长短受到原表上数据量的影响。 同时在此过程中,会发生以下事件:

  • 原表被真正意义上重定义,拥有临时表的所有属性、索引、约束、授权和触发器。
  • 已注册的依赖对象会被自动重命名
  • 临时表上的参考约束(referential constraint)会牵涉到重定义后的表上,且这些约束会被自动启用。

 

若重定义以rowid 方式完成,那么重定义后的表上会出现一个隐藏字段叫做M_ROW$$,我们推荐将该隐藏字段设置为unused:

 

ALTER TABLE table_name SET UNUSED (M_ROW$$)

 

DBMS_REDEFINITION.FINISH_REDEF_TABLE过程的详细定义如下:

 

  -- NAME:     finish_redef_table - complete the online re-organization
  -- INPUTS:   uname        - schema name
  --           orig_table   - name of table to be re-organized
  --           int_table    - name of interim table
  --           part_name    - name of the partition being redefined
  PROCEDURE finish_redef_table(uname          IN VARCHAR2,
                               orig_table     IN VARCHAR2,
                               int_table      IN VARCHAR2,
                               part_name      IN VARCHAR2 := NULL);

 

以上我们了解了一个在线重定义的主要步骤,以及10g中引入的一些新的procedure和有用视图,接下来我们实际操作一个在线重定义示范:

 

原表的定义和数据量如下:

 

create table SH.SALES
(
  PROD_ID       NUMBER not null,
  CUST_ID       NUMBER not null,
  TIME_ID       DATE not null,
  CHANNEL_ID    NUMBER not null,
  PROMO_ID      NUMBER not null,
  QUANTITY_SOLD NUMBER(10,2) not null,
  AMOUNT_SOLD   NUMBER(10,2) not null
)

alter table SH.SALES
  add constraint SALES_CHANNEL_FK foreign key (CHANNEL_ID)
  references SH.CHANNELS (CHANNEL_ID);
alter table SH.SALES
  add constraint SALES_CUSTOMER_FK foreign key (CUST_ID)
  references SH.CUSTOMERS (CUST_ID);
alter table SH.SALES
  add constraint SALES_PRODUCT_FK foreign key (PROD_ID)
  references SH.PRODUCTS (PROD_ID);
alter table SH.SALES
  add constraint SALES_PROMO_FK foreign key (PROMO_ID)
  references SH.PROMOTIONS (PROMO_ID);
alter table SH.SALES
  add constraint SALES_TIME_FK foreign key (TIME_ID)
  references SH.TIMES (TIME_ID);
-- Create/Recreate indexes
create bitmap index SH.SALES_CHANNEL_BIX on SH.SALES (CHANNEL_ID);
create bitmap index SH.SALES_CUST_BIX on SH.SALES (CUST_ID);
create bitmap index SH.SALES_PROD_BIX on SH.SALES (PROD_ID);
create bitmap index SH.SALES_PROMO_BIX on SH.SALES (PROMO_ID);
create bitmap index SH.SALES_TIME_BIX on SH.SALES (TIME_ID);

SQL> select count(*) from sh.sales;

  COUNT(*)
----------
    918843

 

现在希望在原表的基础上增加默认为188的number类型maclean字段,且将该表转换为按照range (TIME_ID)范围分区的分区表。

 

因为该表上有7*24的更新业务如下,所以只能使用在线重定义方式,且因为该表上没有Primary key,所以只能使用rowid的重定义方式:

 

begin
  loop
    insert into sh.sales
    values
      (42, 938, to_date('1998-01-01', 'YYYY-MM-DD'), 2, 999, 1, 800);
    insert into sh.sales
    values
      (42, 938, to_date('1998-01-01', 'YYYY-MM-DD'), 2, 999, 1, 800);
    delete sh.sales where rownum = 1;
    commit;
    dbms_lock.sleep(0.5);
  end loop;
end;

 

1. 利用can_redef_table存储过程验证原表是否可以以rowid方式重定义:

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn & www.askmac.cn

SQL> conn sh/sh
Connected.

SQL> begin
  2    dbms_redefinition.can_redef_table(uname        => 'SH',
  3                                      tname        => 'SALES',
  4                                      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
  5  end;
  6  /
begin
*
ERROR at line 1:
ORA-12091: cannot online redefine table "SH"."SALES" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 2

 

发现SALES表上有物化视图,这回导致online redefine无法进行,找出物化视图并drop掉,完成重定义后可以重建这些materialized view:

 

SQL> select mview_name from dba_mviews where owner = 'SH';

MVIEW_NAME
------------------------------
FWEEK_PSCAT_SALES_MV
CAL_MONTH_SALES_MV

SQL> drop materialized view CAL_MONTH_SALES_MV;

Materialized view dropped.

SQL> drop  materialized view  FWEEK_PSCAT_SALES_MV;

Materialized view dropped.

SQL> begin
  2    dbms_redefinition.can_redef_table(uname        => 'SH',
  3                                      tname        => 'SALES',
  4                                      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
  5  end;
  6  /

PL/SQL procedure successfully completed.

 

 

再次验证成功。

 

2. 创建空的临时表,在原表的基础上加入MACLEAN字段以及分区定义:

 

create table SH.INT_SALES
(
  PROD_ID       NUMBER not null,
  CUST_ID       NUMBER not null,
  TIME_ID       DATE not null,
  CHANNEL_ID    NUMBER not null,
  PROMO_ID      NUMBER not null,
  QUANTITY_SOLD NUMBER(10,2) not null,
  AMOUNT_SOLD   NUMBER(10,2) not null,
  MACLEAN       NUMBER(10,2) default 188 not null
)
partition by range (TIME_ID)
(
  partition SALES_1995 values less than (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace EXAMPLE
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_1996 values less than (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace EXAMPLE
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_H1_1997 values less than (TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace EXAMPLE
    pctfree 0
    initrans 1
    maxtrans 255,
...................................

建表DDL过长,以上节选主要部分

 

并在会话级别启用FORCE PARALLEL:

 

alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;

 

3.调用DBMS_REDEFINITION.START_REDEF_TABLE存储过程启动重定义进程

 

SQL> set timing on;
begin
  DBMS_REDEFINITION.START_REDEF_TABLE(uname        => 'SH',
                                      orig_table   => 'SALES',
                                      int_table    => 'INT_SALES',
                                      col_mapping  => 'PROD_ID PROD_ID,CUST_ID CUST_ID,TIME_ID TIME_ID,CHANNEL_ID CHANNEL_ID,PROMO_ID PROMO_ID,QUANTITY_SOLD QUANTITY_SOLD,AMOUNT_SOLD AMOUNT_SOLD',
                                      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
end;
PL/SQL procedure successfully completed.

Elapsed: 00:00:04.23

SQL> select count(*) from int_sales;

  COUNT(*)
----------
    921539

Elapsed: 00:00:00.23

 

4. 调用COPY_TABLE_DEPENDENTS过程克隆依赖对象:

 

SQL> DECLARE
  2    num_errors PLS_INTEGER;
  3  BEGIN
  4    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname            => 'SH',
  5                                            orig_table       => 'SALES',
  6                                            int_table        => 'INT_SALES',
  7                                            copy_indexes     => DBMS_REDEFINITION.cons_orig_params,
  8                                            copy_triggers    => TRUE,
  9                                            copy_constraints => FALSE,
 10                                            copy_privileges  => TRUE,
 11                                            ignore_errors    => FALSE,
 12                                            num_errors       => num_errors,
 13                                            copy_statistics  => TRUE);
 14  END;
 15  /
DECLARE
*
ERROR at line 1:
ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1173
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1712
ORA-06512: at line 4

Elapsed: 00:00:00.06

SQL> select * from DBA_REDEFINITION_ERRORS;

OBJECT_TYP OBJECT_OWNER                   OBJECT_NAME
---------- ------------------------------ ------------------------------
BASE_TABLE_OWNER               BASE_TABLE_NAME
------------------------------ ------------------------------
DDL_TXT
--------------------------------------------------------------------------------
INDEX      SH                             SALES_CHANNEL_BIX
SH                             SALES
CREATE BITMAP INDEX "SH"."TMP$$_SALES_CHANNEL_BIX0" ON "SH"."INT_SALES" ("CHANNE

 

因为原表上有bitmap indexes,而目标的partitioned tables(分区表)仅支持LOCAL bitmap indexes, 这里可以通过REGISTER_DEPENDENT_OBJECT来注册LOCAL bitmap indexes依赖对象,作为教学示例我们不这样做,而选择不克隆索引类型的依赖对象,指定copy_indexes参数为0:

 

SQL> DECLARE
  2    num_errors PLS_INTEGER;
  3  BEGIN
  4    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname            => 'SH',
  5                                            orig_table       => 'SALES',
  6                                            int_table        => 'INT_SALES',
  7                                            copy_indexes     => 0,
  8                                            copy_triggers    => TRUE,
  9                                            copy_constraints => FALSE,
 10                                            copy_privileges  => TRUE,
 11                                            ignore_errors    => FALSE,
 12                                            num_errors       => num_errors,
 13                                            copy_statistics  => TRUE);
 14  END;
 15  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.03

 

5. 利用sync_interim_table过程同步临时表的数据减少finish_redef_table的耗时:

 

SQL> select count(*) from sales;

  COUNT(*)
----------
    923046

Elapsed: 00:00:00.01

SQL> select count(*) from int_sales;

  COUNT(*)
----------
    921539

Elapsed: 00:00:00.24

SQL> begin
  2    dbms_redefinition.sync_interim_table(uname      => 'SH',
  3                                         orig_table => 'SALES',
  4                                         int_table  => 'INT_SALES');
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.87

SQL> select count(*) from int_sales;

  COUNT(*)
----------
    923135

 

6.执行finish_redef_table过程完成重定义:

 

begin
  dbms_redefinition.finish_redef_table(uname      => 'SH',
                                       orig_table => 'SALES',
                                       int_table  => 'INT_SALES');
end;
/

SQL> desc sales;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID                                   NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 TIME_ID                                   NOT NULL DATE
 CHANNEL_ID                                NOT NULL NUMBER
 PROMO_ID                                  NOT NULL NUMBER
 QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
 AMOUNT_SOLD                               NOT NULL NUMBER(10,2)
 MACLEAN                                   NOT NULL NUMBER(10,2)

SQL>  select count(*) from sales partition (SALES_Q2_2000);

  COUNT(*)
----------
     55515

Elapsed: 00:00:00.02

SQL> select distinct maclean from sales;

   MACLEAN
----------
       188

Elapsed: 00:00:00.32

 

以上成功完成了对SALES表的Online Redefinition,由非分区表在线重定义为分区表且增加了一个字段。

这里因为我们使用rowid方式,所以重定义完的表上会多出一个隐藏字段, 从10.2开始M_ROW$$的隐藏列会被命名为SYS_%DATE%的形式,且默认即为unused状态:

 

SQL> set linesize 90 pagesize 1400

SQL> select *
  2    from dba_tab_cols
  3   where owner = 'SH'
  4     and column_name like 'SYS%'
  5     and table_name='SALES';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
------------------------------
DATA_TYPE
------------------------------------------------------------------------------------------
DAT DATA_TYPE_OWNER                DATA_LENGTH DATA_PRECISION DATA_SCALE N  COLUMN_ID
--- ------------------------------ ----------- -------------- ---------- - ----------
DEFAULT_LENGTH
--------------
DATA_DEFAULT
--------------------------------------------------------------------------------
NUM_DISTINCT LOW_VALUE
------------ ----------------------------------------------------------------
HIGH_VALUE                                                          DENSITY  NUM_NULLS
---------------------------------------------------------------- ---------- ----------
NUM_BUCKETS LAST_ANAL SAMPLE_SIZE CHARACTER_SET_NAME
----------- --------- ----------- --------------------------------------------
CHAR_COL_DECL_LENGTH GLO USE AVG_COL_LEN CHAR_LENGTH C V80 DAT HID VIR SEGMENT_COLUMN_ID
-------------------- --- --- ----------- ----------- - --- --- --- --- -----------------
INTERNAL_COLUMN_ID HISTOGRAM
------------------ ---------------
QUALIFIED_COL_NAME
------------------------------------------------------------------------------------------
SH                             SALES
SYS_C00009_11120703:40:57$
VARCHAR2
                                           255                           Y

                                  CHAR_CS
                 255 NO  NO                      255 B NO  YES YES NO                  9
                 9 NONE
SYS_C00009_11120703:40:57$

==========================================================================================

SQL>    select * from dba_unused_col_tabs ;

OWNER                          TABLE_NAME                          COUNT
------------------------------ ------------------------------ ----------
SH                             SALES                                   1

SQL>  alter table sales drop unused columns;

Table altered.

Elapsed: 00:00:13.36

SQL>    select * from dba_unused_col_tabs ;

no rows selected

 

若在完成重定义(执行finish_redef_table)之前希望中断在线重定义表,则需要使用DBMS_REDEFINITION.ABORT_REDEF_TABLE明确手动中断abort,如:

 

begin
  dbms_redefinition.abort_redef_table(uname      => 'SH',
                                      orig_table => 'SALES',
                                      int_table  => 'INT_SALES');
end;
/

该abort_redef_table过程的详细定义如下:

  -- NAME:     abort_redef_table - clean up after errors or abort the
  --                               online re-organization
  -- INPUTS:   uname        - schema name
  --           orig_table   - name of table to be re-organized
  --           int_table    - name of interim table
  --           part_name    - name of the partition being redefined
  PROCEDURE abort_redef_table(uname        IN VARCHAR2,
                              orig_table   IN VARCHAR2,
                              int_table    IN VARCHAR2,
                              part_name    IN VARCHAR2 := NULL);

沪ICP备14014813号-2

沪公网安备 31010802001379号