ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 在线重定义

oracle 在线重定义

原创 Linux操作系统 作者:sqleee 时间:2009-12-28 15:19:04 0 删除 编辑

在线重定义简介

在一个高可用系统中,如果需要改变一个表的定义是一件比较棘手的问题,尤其是对于7×24系统。Oracle提供的基本语法基本可以满足一般性修改,但是对于把普通堆表改为分区表,把索引组织表修改为堆表等操作就无法完成了。而且,对于被大量DML语句访问的表,幸运的是,Oracle9i版本开始提供了在线重定义表功能,通过调用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的方式,则不能是索引组织表
  • 如果原表上有物化视图或者物化视图日志,则不能在线重定义
  • 物化视图容器表或者高级队列表不能在线重定义
  • 索引组织表的溢出表不能在线重定义
  • 拥有BFILELOGN列的表不能在线重定义
  • Cluster中的表不能在线重定义
  • syssystem下的表不能在线重定义
  • 临时表不能在线重定义
  • 不支持水平数据子集
  • 在列映射时只能使用有确定结果的表达式,如子查询就不行
  • 如果中间表有新增列,则不能有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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: solaris性能监控
下一篇: shell测试
请登录后发表评论 登录
全部评论

注册时间:2008-03-21

  • 博文量
    31
  • 访问量
    114426