分区：在线重定义：DBMS_REDEFINITION

本文基于以下文章实际练习测试：

http://www.oracle-base.com/articles/misc/partitioning-an-existing-table.php

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables007.htm#ADMIN11678

 

一．说明

权限

在线重定义的用户需要相应的权限，具体权限如下：

Execute privileges on theDBMS_REDEFINITION package are granted toEXECUTE_CATALOG_ROLE. In addition to having execute privileges on this package, you must be granted the following privileges:

·         CREATEANYTABLE

·         ALTERANYTABLE

·         DROPANYTABLE

·         LOCKANY TABLE

·         SELECTANYTABLE

The following additional privileges are required to executeCOPY_TABLE_DEPENDENTS:

·         CREATEANYTRIGGER

·         CREATEANYINDEX

此次练测试临时使用了DBA权限，做完了收回。

二．创建模拟环境

1.创建一个小表

CREATE TABLE type_tab (

  id            NUMBER(10),

  description   VARCHAR2(50)

);

主键

ALTER TABLE type_tab ADD (

  CONSTRAINT type_tab_pk PRIMARY KEY (id)

);

 

INSERT INTO type_tab (id, description) VALUES (1, 'ONE');

INSERT INTO type_tab (id, description) VALUES (2, 'TWO');

INSERT INTO type_tab (id, description) VALUES (3, 'THREE');

COMMIT;

2.创建一个大表

SQL>CREATE TABLE redef_table (

  id            NUMBER(10),

  created_date  DATE,

  type_tab_id     NUMBER(10),

  data          VARCHAR2(50)

);

插入大表内容

 DECLARE

  l_type_tab_id    type_tab.id%TYPE;

  l_create_date  DATE;

BEGIN

      FOR i IN 1 .. 100000 LOOP

        IF MOD(i, 3) = 0 THEN

          l_create_date := ADD_MONTHS(SYSDATE, -24);

          l_type_tab_id   := 2;

       ELSIF MOD(i, 2) = 0 THEN

         l_create_date := ADD_MONTHS(SYSDATE, -12);

         l_type_tab_id   := 1;

       ELSE

        l_create_date := SYSDATE;

         l_type_tab_id   := 3;

      END IF;

      

       INSERT INTO redef_table (id, created_date, type_tab_id, data)

       VALUES (i, l_create_date, l_type_tab_id, 'This is some data for ' || i);

     END LOOP;

     COMMIT;

   END;

   /

主键

ALTER TABLE redef_table ADD (

  CONSTRAINT redef_table_pk PRIMARY KEY (id)

);

索引

CREATE INDEX bita_created_date_i ON redef_table(created_date);

CREATE INDEX bita_look_fk_i ON redef_table(type_tab_id);

外键

ALTER TABLE redef_table ADD (

  CONSTRAINT bita_look_fk

  FOREIGN KEY (type_tab_id)

  REFERENCES type_tab(id)

);

统计信息

EXEC DBMS_STATS.gather_table_stats(USER, 'type_tab', cascade => TRUE);

EXEC DBMS_STATS.gather_table_stats(USER, 'redef_table', cascade => TRUE);

三．创建一个分区的临时表

该表应该于要做分区的有相同的字段类型，不同的是这个表是一个分区表。

CREATE TABLE redef_table2 (

  id            NUMBER(10),

  created_date  DATE,

  type_tab_id     NUMBER(10),

  data          VARCHAR2(50)

)

PARTITION BY RANGE (created_date)

(PARTITION redef_table_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),

 PARTITION redef_table_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),

 PARTITION redef_table_2005 VALUES LESS THAN (MAXVALUE));

四．dbms_redefinition在线重定义

如果没给相应权限，会遇到下面的问题，下面是一个简单的troubleshoot过程：

SQL> EXEC dbms_Redefinition.Can_Redef_Table(USER, 'redef_table');

BEGIN dbms_Redefinition.Can_Redef_Table(USER, 'redef_table'); END;

 

      *

ERROR at line 1:

ORA-06550: line 1, column 7:

PLS-00201: identifier 'DBMS_REDEFINITION' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

 

 

SQL> conn /as sysdba

SQL> grant execute on dbms_redefinition to hr;

SQL> conn hr/hr

SQL> exec dbms_redefinition.can_redef_table(user,'redef_table');

BEGIN dbms_redefinition.can_redef_table(user,'redef_table'); END;

 

*

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at "SYS.DBMS_REDEFINITION", line 143

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1627

ORA-06512: at line 1

SQL> conn /as sysdba

Connected.

SQL> grant dba to hr;

--临时使用。可以用具体的权限来代替

SQL> conn hr/hr;

Connected.

Start the Redefintion Process

1.首先测试redefinition的可用性

SQL> exec dbms_redefinition.can_redef_table(user,'redef_table');

PL/SQL procedure successfully completed.

如果无error，说明正常

2.开始redefinition

BEGIN

  DBMS_REDEFINITION.start_redef_table(

    uname      => USER,       

    orig_table => 'redef_table',

    int_table  => 'redef_table2');

END;

/

PL/SQL procedure successfully completed.

Create Constraints and Indexes

3.两个表进行同步

SQL>BEGIN

  dbms_redefinition.sync_interim_table(

    uname      => USER,       

    orig_table => 'redef_table',

    int_table  => 'redef_table2');

END;

/

 

PL/SQL procedure successfully completed.

测试

SQL> select count(*) from redef_table2;

 

  COUNT(*)

----------

    100000

 

SQL> select count(*) from redef_table;

 

  COUNT(*)

----------

    100000

4.在临时表上创建constraints and indexes

ALTER TABLE redef_table2 ADD (

  CONSTRAINT redef_table_pk2 PRIMARY KEY (id)

);

CREATE INDEX bita_created_date_i2 ON redef_table2(created_date) LOCAL;

CREATE INDEX bita_look_fk_i2 ON redef_table2(type_tab_id) LOCAL;

ALTER TABLE redef_table2 ADD (

  CONSTRAINT bita_look_fk2

  FOREIGN KEY (type_tab_id)

  REFERENCES type_tab(id)

);

收集统计信息

EXEC DBMS_STATS.gather_table_stats(USER, 'redef_table2', cascade => TRUE);

 

对表插入新的数据，在后面进行测试。

SQL> insert into redef_table

  2  select id+3000000,created_date,type_tab_id,data from redef_table where rownum<=2;

2 rows created.

SQL> commit;

Complete the Redefintion Process

BEGIN

  dbms_redefinition.finish_redef_table(

    uname      => USER,       

    orig_table => 'redef_table',

    int_table  => 'redef_table2');

END;

  7  /

 

PL/SQL procedure successfully completed.

进行数据测试

SQL> select count(*) from redef_table;

  COUNT(*)

----------

    100002

SQL> select count(*) from redef_table2;

  COUNT(*)

----------

    100002

 

在上述操作完成后。在数据字典中，临时表和要改变的进行了交换，之前的生产表从名字上变成了临时表，而临时表在名字变成了生产表。但相关的约束索引的名称未变。

清理掉目前的“临时表”

SQL> DROP TABLE redef_table2;

修改相应的约束和索引的名称

ALTER TABLE redef_table RENAME CONSTRAINT redef_table_pk2 TO redef_table_pk;

ALTER TABLE redef_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;

ALTER INDEX redef_table_pk2 RENAME TO redef_table_pk;

ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;

ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;

五．测试

SQL> SELECT partitioned

  2  FROM   user_tables

  3  WHERE  table_name = 'redef_table';

 

 

PAR

---

YES

 

SQL> SQL> SELECT partition_name

  2  FROM   user_tab_partitions

  3  WHERE  table_name = 'redef_table';

 

 

PARTITION_NAME

------------------------------

redef_table_2003

redef_table_2004

redef_table_2005