ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于在线重定义表

关于在线重定义表

原创 Linux操作系统 作者:NinGoo 时间:2019-03-09 09:12:07 0 删除 编辑

Oracle9i开始引入在线重定义表(Redefine Tables Online)的功能,通过调用dbms_redefinition包,可以在修改表结构的同时允许DML操作。


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

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

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

  • execute_catalog_role
  • create any table
  • alter any table
  • drop any table
  • lock any table
  • select any table

在线重定义表的步骤:

1.选择在线重定义的方法
方法一:基于主键。要求原表和重定义后的表有相同的主键列。这是默认方法
方法二:基于ROWID。该方法不能用于索引组织表(IOT),并且在重定义的表中会添加隐藏列(M_ROW$$),建议将该列标记为unused或删除。

2.调用dbms_redefinition.can_redef_table(),检查原表是否能进行在线重定义。
SQL> conn / as sysdba
已连接。

SQL> exec dbms_redefinition.can_redef_table(user,'TEST');
BEGIN dbms_redefinition.can_redef_table(user,'TEST'); END;

*
第 1 行出现错误:
ORA-12087: 在 "SYS" 拥有的表上不允许联机重新定义
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 137
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: 在 line 1

SQL> create user ning identified by ning;

用户已创建。

SQL> grant connect,resource to ning;

授权成功。

SQL> grant execute_catalog_role to ning;

授权成功。

SQL> conn ning/ning
已连接。

SQL> create table test(id int,name varchar2(20));

表已创建。

SQL> exec dbms_redefinition.can_redef_table(user,'TEST');
BEGIN dbms_redefinition.can_redef_table(user,'TEST'); END;

*
第 1 行出现错误:
ORA-12089: 不能联机重新定义无主键的表 "NING"."TEST"
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 137
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: 在 line 1

SQL> alter table test add constraint con_test_pk primary key(id);

表已更改。

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

PL/SQL 过程已成功完成。

Ok,表ning.test符合使用基于主键方式的在线重定义功能了。

3.在原表同一个schema下建一个空的中间表。根据重定义后你期望得到的结构建立中间表。比如:采用分区表,增加了COLUMN等。
SQL> create table test1(id int,name varchar2(20),address varchar(30));

表已创建。

4.调用dbms_redefinition.start_redef_table()
PROCEDURE START_REDEF_TABLE
参数名称 类型 输入/输出默认值?
------------------------- ---------------------- ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
COL_MAPPING VARCHAR2 IN DEFAULT
OPTIONS_FLAG BINARY_INTEGER IN DEFAULT
ORDERBY_COLS VARCHAR2 IN DEFAULT
PART_NAME VARCHAR2 IN DEFAULT
主要参数:
ORIG_TABLE:原表
INT_TABLE:中间表
COL_MAPPING:列的映射关系。默认所有包括在中间表中的列用于表的重定义。如果给出了映射方法,则只考虑映射方法中给出的列
OPTIONS_FLAG:重定义的方法,默认基于主键。dbms_redefinition.cons_use_pk和dbms_redefinition.cons_use_rowid

SQL> exec dbms_redefinition.start_redef_table(user,'TEST','TEST1');
BEGIN dbms_redefinition.start_redef_table(user,'TEST','TEST1'); END;

*
第 1 行出现错误:
ORA-01031: 权限不足
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 50
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: 在 line 1

SQL> conn / as sysdba
已连接。

SQL> grant create any table to ning;

授权成功。

SQL> grant alter any table to ning;

授权成功。

SQL> grant drop any table to ning;

授权成功。

SQL> grant lock any table to ning;

授权成功。

SQL> grant select any table to ning;

授权成功。

SQL> conn ning/ning
已连接。

SQL> exec dbms_redefinition.start_redef_table(user,'TEST','TEST1');

PL/SQL 过程已成功完成。

5.在中间表上建立触发器、索引和约束,并进行相应的授权。任何包含中间表的引用约束应将状态置为disabled。当重定义完成时,中间表上建立的触发器、索引、约束和授权将替换重定义表上的触发器、索引、约束和授权。中间表上disabled的约束将在重定义表上enable。
SQL> create index ix_test on test1(name);

索引已创建。

6.(可选)如果在执行dbms_redefinition.start_redef_table()过程和执行dbms_redefinition.finish_redef_table()过程直接在重定义表上执行了大量的DML操作,那么可以选择执行一次或多次的sync_interim_table()过程,以减少最后一步执行finish_redef_table()过程时的锁定时间。因为执行finish_redef_table()时会将原表锁定一小段时间,如果大量DML操作存在,会时锁定时间加长。
SQL> exec dbms_redefinition.sync_interim_table(user,'TEST','TEST1');

PL/SQL 过程已成功完成。

7.执行dbms_redefinition.finish_redef_table(),完成重定义,中间表变成原重定义表,原表则变成了中间表
SQL> exec dbms_redefinition.finish_redef_table(user,'TEST','TEST1');

PL/SQL 过程已成功完成。

SQL> desc test
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

ID NUMBER(38)
NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
SQL> desc test1
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER(38)
NAME VARCHAR2(20)


8.(可选)可以重命名索引、触发器和约束。对于采用了ROWID方式重定义的表,包括了一个隐含列M_ROW$$。推荐使用下列语句经隐含列置为UNUSED状态或删除。
ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);
ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;

在线重定义后的结果:

  • 原表已经根据中间表的结构重新定义
  • 在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

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

请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    200
  • 访问量
    131908