oracle 在线重定义

在线重定义简介：

在一个高可用系统中，如果需要改变一个表的定义是一件比较棘手的问题，尤其是对于7×24系统。Oracle提供的基本语法基本可以满足一般性修改，但是对于把普通堆表改为分区表，把索引组织表修改为堆表等操作就无法完成了。而且，对于被大量DML语句访问的表，幸运的是，Oracle从9i版本开始提供了在线重定义表功能，通过调用DBMS_REDEFINITION包，可以在修改表结构的同时允许DML操作。

基本原理：

把原来的表与现在的新表换个名字，换名操作仅仅是操作数据字典

在线重定义表的主要功能：

• 修改表的存储参数
• 在同一个schema下将表移动到不同的tablespace
• 增加并行查询支持
• 添加或删除分区支持
• 重建表以减少碎片
• 将堆表变为索引组织表或相反
• 添加或删除列

在线重定义的好处：

• 重组表数据，压缩空间
• 在线把表从一个表空间迁移到另一个表空间
• 把表从普通表转换成分区表或者从分区表转换成普通表
• 在线删除字段
• 修改表的一些参数，一些需要重组才能起效的特性，如initrans参数

在线重定义表的需要以下权限：

• execute_catalog_role(角色)
• create any table
• alter any table
• drop any table
• lock any table
• select any table

在线重定义后的结果:

• 原表已经根据中间表的结构重新定义
• 在start_redef_table()和finish_redef_table()之间定义在中间表上的触发器、索引、约束和授权，现在定义在原始重定义表上。中间表上disabled的约束在原始表上处于enabled状态。
• 原始表上定义的触发器、索引、约束和授权建立在中间表上，并会在删除中间表时删除。原始表上原来enabled状态的索引，建立在中间表上，并处于disabled状态。
• 任何定义在原始表上的存储过程和游标都会变为INVALID，当下次调用时后自动进行编译。
• 如果执行过程中出现错误或者人为选择退出的话，可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE()过程。

在线重定义的一些限制：

• 如果使用基于主键的方式，则原表后重定义后的表必须有相同的主键
• 如果使用基于ROWID的方式，则不能是索引组织表
• 如果原表上有物化视图或者物化视图日志，则不能在线重定义
• 物化视图容器表或者高级队列表不能在线重定义
• 索引组织表的溢出表不能在线重定义
• 拥有BFILE，LOGN列的表不能在线重定义
• Cluster中的表不能在线重定义
• sys和system下的表不能在线重定义
• 临时表不能在线重定义
• 不支持水平数据子集
• 在列映射时只能使用有确定结果的表达式，如子查询就不行
• 如果中间表有新增列，则不能有NOT NULL约束
• 原表和中间表之间不能有引用完整性
• 在线重定义无法采用nologging

实践 1： 把普通堆表转换成分区表

我有源表stu有如下结构，我准备按stu.id按range分区

SQL> select t.table_name,
  2         t.tablespace_name,
  3         t.partitioned
  4    from user_tables t
  5   where t.table_name = 'STU';

TABLE TABLE PARTI
----- ----- -----
STU   SQLEE NO

SQL> desc stu
Name Type        Nullable Default Comments
---- ----------- -------- ------- --------
ID   NUMBER                               
NAME VARCHAR2(8) Y                        
SEX  NUMBER      Y                        

SQL> select count(*) from stu;

  COUNT(*)
----------
         5

SQL> select c.owner,
  2  c.constraint_name,
  3  c.constraint_type,
  4  c.table_name
  5  from user_constraints c;

OWNER CONSTRAINT_NAME CONST TABLE
----- --------------- ----- -----
SQLEE FK_STU_SEX      R     STU
SQLEE PK_STU_ID       P     STU

SQL> select i.index_name,
  2  i.table_type,
  3  i.table_owner,
  4  i.table_name
  5  from user_indexes i
  6  ;

INDEX_NAME      TABLE_TYPE  TABLE TABLE
--------------- ----------- ----- -----
IN_STU_NAME     TABLE       SQLEE STU
PK_STU_ID       TABLE       SQLEE STU

1.验证表stu是否可用于重定义

SQL> exec dbms_redefinition.can_redef_table('SQLEE','STU');

PL/SQL procedure successfully completed

2.创建中间分区表

SQL> create table stu_partition
  2   (ID   NUMBER not null,
  3    NAME VARCHAR2(8),
  4    SEX  NUMBER)
  5  partition by range(id)
  6    (partition stu_part1 values less than(2),
  7     partition stu_part2 values less than(3),
  8     partition stu_part3 values less than(4),
  9     partition stu_part4 values less than(5),
 10     partition stu_part5 values less than(maxvalue)
 11    );

Table created

3.执行表的在线重定义

SQL> exec dbms_redefinition.start_redef_table('SQLEE','STU','stu_partition');

PL/SQL procedure successfully completed

4.在中间表上创建与源表相同的约束，索引，触发器等

SQL> alter table STU_PARTITION
  2    add constraint PK_STU_PARTITION_ID primary key (ID)
  3    using index
  4    tablespace SQLEE;

Table altered

SQL> alter table STU_PARTITION
  2    add constraint FK_STU_PARTITION_SEX foreign key (SEX)
  3    references SEX (ID) on delete cascade
  4    deferrable initially deferred;

Table altered

SQL> create unique index IN_STU_PARTITION_NAME on STU_PARTITION (NAME)
  2    tablespace SQLEE;

Index created

5.执行源表与中间表的数据同步

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SQLEE','STU','STU_PARTITION');

PL/SQL procedure successfully completed

6.结束重定义过程

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SQLEE','STU','STU_PARTITION');

PL/SQL procedure successfully completed

7.检测表stu分区是否成功。

SQL> select p.table_name,
  2         p.partition_name,
  3         p.high_value
  4    from user_tab_partitions p
  5   where p.table_name = 'STU';

TABLE PARTITION_ HIGH_VALUE
----- ---------- ----------
STU   STU_PART1  2
STU   STU_PART2  3
STU   STU_PART3  4
STU   STU_PART4  5
STU   STU_PART5  MAXVALUE

SQL> select pt.table_name,
  2         pt.partitioning_type,
  3         pt.partition_count
  4    from user_part_tables pt
  5   where pt.table_name = 'STU';

TABLE PARTITIONING_TYPE PARTITION_COUNT
----- ----------------- ---------------
STU   RANGE                           5

SQL> select count(*) from stu;

  COUNT(*)
----------
         5
8.删除中间表

SQL> drop table stu_partition purge;

Table dropped

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

转载于:http://blog.itpub.net/12158104/viewspace-623767/