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

时间:2022-08-30 07:55:02

通常我们了解到的在线重定义的表的方式有几种:

1.9i使用rowid的重定义方式

2.10G开始可以使用oracle 自带的 DBMS_REDEFINITION Package进行重定义

 

9i 这种通过rowid方式,在执行完在线重定义后,表的名称虽然保持不变,但是索引、约束等关联对象的名称会发生变化,如果这个表的数据量非常大,重建索引和触发器就是个很麻烦的活,同时当dba想将大表进行拆分,也可以将目标表,重定义成分区表,方便将来的维护管理,下面就DBMS_REDEFINITION方法实验过程如下:

 

模拟生产数据表

 create table t as select object_name,object_id from dba_objects;

 
生产表上必须要有主键,此处我们模拟加上主键和索引

alter table t add constraint pk_id primary key(object_id);

CREATE INDEX IND_T_NAME ON T (OBJECT_NAME);


检查索引和约束所在位置

INDEX_NAME            INDEX_TYPE           TABLE_OWNER              TABLE_NAME                   
--------------------- -------------------- ------------------------ ------------------------------
PK_ID                         NORMAL               SCOTT                                  T                           
IND_T_NAME            NORMAL               SCOTT                                  T    

 
 准备迁移分区表

create table RED_T(object_id number,object_name varchar2(30))
 partition by range(object_id)
 (partition p1 values less than(50000),
 partition p2 values less than(100000),
 partition p3 values less than(150000),
 partition p4 values less than(200000),
 partition pmax values less than(maxvalue));

  
检查生产表T是否满足重定义的条件

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'T', DBMS_REDEFINITION.CONS_USE_PK);

 

开始执行2个表间的重定义操作

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'RED_T');

 

执行数据同步

execute dbms_redefinition.sync_interim_table(user,'T','RED_T')

 

完成重定义

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T', 'RED_T');

 

查询重定义后的结果

SQL> select partition_name,high_value,tablespace_name
  2  from user_tab_partitions where table_name='RED_T'
  3  order by partition_position;

 

未选定行

SQL> select partition_name,high_value,tablespace_name
  2  from user_tab_partitions where table_name='T'
  3  order by partition_position;

PARTITION_NAME                 HIGH_VALUE                           TABLESPACE_NAME
------------------------------ -------------------------------------------- ----------------------
P1                             50000                                USERS
P2                             100000                              USERS
P3                             150000                              USERS
P4                             200000                              USERS
PMAX                         MAXVALUE                      USERS

  我们发现原来RED_T才是分区表,而t原来是个普通表,重定义之后,RED_T变成了分区表。 这个时候我们可以将表t删除,并重命名表RED_T为t。


检查索引信息   

SQL> select index_name,index_type,table_owner,table_name from user_indexes where table_name like 'T%';                                                                                         
                                                                                                                       
INDEX_NAME            INDEX_TYPE           TABLE_OWNER              TABLE_NAME                   
--------------------- -------------------- ------------------------ ------------------------------
PK_ID                          NORMAL               SCOTT                    RED_T                           
IND_T_NAME            NORMAL                SCOTT                    RED_T     

可以发现原来表t的索引和逐渐也迁移到了新表RED_T上。