dbms_redefinition方便地改普通表为分区表

以前将一个普通的表改成一个分区表，做法是手工创建一人中间表，然后改表名。ORACLE10G已经提供了对应用透明的切换方法，那就dbms_redefinition包。
表的定义见http://psoug.org/reference/dbms_redefinition.html。
这里有一个小帖示：如果要找一个包的定义可以通过以下方法：
http://psoug.org/reference/包名.html

言归正传，看看如何使用吧。
1、首先创建一个大表，为什么要创建一个大表？哦，表大了才会想分区嘛，所以测试就用个大表吧。
以下是创建大表的脚本（原创是TOM大叔哦）：
bit_table.sql:
create table big_table
as
select rownum id, a.*
  from all_objects a
 where 1=0
/
alter table big_table nologging;

declare
    l_cnt number;
    l_rows number := &1;
begin
    insert /*+ append */
    into big_table
    select rownum, a.*
      from all_objects a
     where rownum <= &1;

    l_cnt := sql%rowcount;

    commit;

    while (l_cnt < l_rows)
    loop
        insert /*+ APPEND */ into big_table
        select rownum+l_cnt,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
          from big_table
         where rownum <= l_rows-l_cnt;
        l_cnt := l_cnt + sql%rowcount;
        commit;
    end loop;
end;
/

alter table big_table add constraint
big_table_pk primary key(id)
/

begin
   dbms_stats.gather_table_stats
   ( ownname    => user,
     tabname    => 'BIG_TABLE',
     method_opt => 'for all indexed columns',
     cascade    => TRUE );
end;
/
select count(*) from big_table;


sql >conn scott/scott;
sql >@d:\big_table.sql;
创建的过程中会让输入&1的值，要往表是放多少条数据，测试就用户1000000吧。

2、创建一个中间表：
      这里要确定其表结构与大表的表结构一致！！
     desc big_table;
desc big_TABLE
名称             空值       类型          
-------------- -------- ------------
ID             NOT NULL NUMBER      
OWNER          NOT NULL VARCHAR2(30)
OBJECT_NAME    NOT NULL VARCHAR2(30)
SUBOBJECT_NAME          VARCHAR2(30)
OBJECT_ID      NOT NULL NUMBER      
DATA_OBJECT_ID          NUMBER      
OBJECT_TYPE             VARCHAR2(19)
CREATED        NOT NULL DATE        
LAST_DDL_TIME  NOT NULL DATE        
TIMESTAMP               VARCHAR2(19)
STATUS                  VARCHAR2(7) 
TEMPORARY               VARCHAR2(1) 
GENERATED               VARCHAR2(1) 
SECONDARY               VARCHAR2(1)


中间表建表语句：
CREATE TABLE PART_TABLE
(
ID              NUMBER NOT NULL,     
OWNER           VARCHAR2(30) NOT NULL,
OBJECT_NAME     VARCHAR2(30) NOT NULL,
SUBOBJECT_NAME          VARCHAR2(30),
OBJECT_ID       NUMBER NOT NULL,     
DATA_OBJECT_ID          NUMBER,     
OBJECT_TYPE             VARCHAR2(19),
CREATED         DATE NOT NULL,    
LAST_DDL_TIME   DATE NOT NULL,      
TIMESTAMP               VARCHAR2(19),
STATUS                  VARCHAR2(7),
TEMPORARY               VARCHAR2(1),
GENERATED               VARCHAR2(1),
SECONDARY               VARCHAR2(1)
)PARTITION BY RANGE (ID)
(
 PARTITION P1 VALUES LESS THAN (100000),
 PARTITION P2 VALUES LESS THAN (200000),
 PARTITION P3 VALUES LESS THAN (300000),
 PARTITION P4 VALUES LESS THAN (400000),
 PARTITION P4 VALUES LESS THAN (500000),
 PARTITION P4 VALUES LESS THAN (600000),
 PARTITION P4 VALUES LESS THAN (700000),
 PARTITION P4 VALUES LESS THAN (800000),
 PARTITION P4 VALUES LESS THAN (900000),
 PARTITION P5 VALUES LESS THAN (MAXVALUE)
);

3、给要做切换的用户权限，所需权限如下：
sql > conn sys/sys as sysdba;
   GRANT create session TO scott;
GRANT create materialized view TO scott;
GRANT create table TO scott;
GRANT create trigger TO scott;
GRANT create view TO scott;

GRANT execute ON dbms_redefinition TO scott;
GRANT alter any table TO scott;
GRANT create any table TO scott;
GRANT drop any table TO scott;
GRANT lock any table TO scott;
GRANT select any table TO scott;
4、 验证是否可以进行在线重定义：

sql> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT', 'BIG_TABLE', DBMS_REDEFINITION.CONS_USE_PK);

35、执行表的在线重定义：

sql> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT', 'BIG_TABLE', 'PART_TABLE');

6、 执行结束在线定义过程

sql> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'BIG_TABLE', 'PART_TABLE');

7、查看数据字典，可以看到改表已经成为了分区表。
sql> select table_name,partition_name,high_value  from user_tab_partitions;

结果会发现中间分区表变成了普通表，而原来的大表变成了分区表。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23065269/viewspace-718729/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23065269/viewspace-718729/