ITPub博客

首页 > 数据库 > 数据库开发技术 > 在线重定义表

在线重定义表

原创 数据库开发技术 作者:feimei 时间:2007-04-10 17:07:57 0 删除 编辑
在线重定义表
修改表的存储参数;
可以将表转移到其他表空间;
增加并行查询选项;
增加或删除分区;
重建表以减少碎片;
将堆表改为索引组织表或相反的操作;
增加或删除一个列。[@more@]

在线重定义表
修改表的存储参数;
可以将表转移到其他表空间;
增加并行查询选项;
增加或删除分区;
重建表以减少碎片;
将堆表改为索引组织表或相反的操作;
增加或删除一个列。

DBMS_REDEFINITION
一.分配权限
grant execute_catalog_role to username;

二.分步完成表在线重定义
1.选择一种重定义方法:
存在两种重定义方法,一种是基于主键、另一种是基于ROWID。
ROWID的方式不能用于索引组织表,而且重定义后会存在隐藏列M_ROW$$。
默认采用主键的方式。

2.调用DBMS_REDEFINITION.CAN_REDEF_TABLE()过程,如果表不满足重定义的条件,将会报错并给出原因。

3.在用一个方案中建立一个空的中间表,根据重定义后你期望得到的结构建立中间表。比如:采用分区表,增加了COLUMN等。

4.调用DBMS_REDEFINITION.START_REDEF_TABLE()过程,调用DBMS_REDEFINITION.START_REDEF_TABLE()过程,
并提供下列参数:被重定义的表的名称、中间表的名称、列的映射规则、重定义方法。
如果映射方法没有提供,则认为所有包括在中间表中的列用于表的重定义。如果给出了映射方法,则只考虑映射方法中给出的列。
如果没有给出重定义方法,则认为使用主键方式。

5.在中间表上建立触发器、索引和约束,并进行相应的授权。任何包含中间表的完整性约束应将状态置为disabled。
当重定义完成时,中间表上建立的触发器、索引、约束和授权将替换重定义表上的触发器、索引、约束和授权。
中间表上disabled的约束将在重定义表上enable。

6.执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程完成表的重定义。
这个过程中,原始表会被独占模式锁定一小段时间,这个段时间是独立与原数据表。

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

ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);


8.操作过程
1)原始表根据中间表的属性和特性进行重定义;
2)START_REDEF_TABLE()和FINISH_REDEF_TABLE()操作之间在中间表上建立的触发器、索引、约束和授权,
现在定义在原始表上。中间表上disabled的约束在原始表上处于enabled状态。
3)原始表上定义的触发器、索引、约束和授权建立在中间表上,并会在删除中间表时删除。
原始表上原来enabled状态的索引,建立在中间表上,并处于disabled状态。
4)任何定义在原始表上的存储过程和游标都会变为INVALID,当下次调用时后自动进行编译。
5)如果执行过程中出现错误或者人为选择退出的话,可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE()过程。

This example illustrates online redefinition of the previously created table hr.admin_emp, which at this point only contains columns: empno, ename, job, deptno. The table is redefined as follows:

New columns mgr, hiredate, sal, and bonus (these existed in the original table but were dropped in previous examples) are added.
The new column bonus is initialized to 0
The column deptno has it's value increased by 10.
The redefined table is partitioned by range on empno.


The steps in this redefinition are illustrated below.

1.Verify that the table is a candidate for online redefinition.

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('silence','admin_emp',
dbms_redefinition.cons_use_pk);
END;
/


2.Create an interim table hr.int_admin_emp.

CREATE TABLE hr.int_admin_emp
(empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
deptno NUMBER(3) NOT NULL,
bonus NUMBER (7,2) DEFAULT(1000))
PARTITION BY RANGE(empno)
(PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);


3.Start the redefinition process.

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('silence', 'admin_emp','int_admin_emp',
'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',
dbms_redefinition.cons_use_pk);
END;
/


4.Create any triggers, indexes and constraints on hr.int_admin_emp.
During the final step of redefinition, these are transferred back to the original table.
Any referential constraints involved on hr.int_admin_emp should be disabled.
You can define any grants associated with the interim table.
These replace the grants on the original table after the redefinition.

ALTER TABLE hr.int_admin_emp ADD CONSTRAINT admin_dept_fkey2
FOREIGN KEY (deptno) REFERENCES hr.departments (department_id);
ALTER TABLE hr.int_admin_emp MODIFY CONSTRAINT admin_dept_fkey2
DISABLE KEEP INDEX;


The disabled constraint, admin_dept_fkey2, will be enabled automatically as part of the finish redefinition process and will then involve the newly redefined admin_emp table.

5.Optionally, synchronize the interim table hr.int_admin_emp.

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/


6.Complete the redefinition.

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/


The table hr.admin_emp is locked in the exclusive mode only for a small window toward the end of this step.
After this call the table hr.admin_emp is redefined such that it has all the attributes of the hr.int_admin_emp table.

Drop the interim table.

Restrictions
The following restrictions apply to the online redefinition of tables:

If the table is to be redefined using primary keys, then the table to be redefined and the post-redefinition table must have the same primary key columns. If the table is to be redefined using ROWIDs, then the table must not be an index-organized table.
Tables that have materialized views and materialized view logs defined on them cannot be online redefined.
Tables that are materialized view container tables and Advanced Queuing tables cannot be online redefined.
The overflow table of an index-organized table cannot be online redefined.
Tables with user-defined types (objects, REFs, collections, typed tables) cannot be online redefined.
Tables with BFILE columns cannot be online redefined.
Tables with LONG columns cannot be online redefined. Tables with LOB columns are acceptable.
The table to be redefined cannot be part of a cluster.
Tables in the SYS and SYSTEM schema cannot be online redefined.
Temporary tables cannot be redefined.
There is no horizontal subsetting support.
Only simple deterministic expressions can be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.
If new columns (which are not instantiated with existing data for the original table) are being added as part of the redefinition, then they must not be declared NOT NULL until the redefinition is complete.
There cannot be any referential constraints between the table being redefined and the interim table.
Table redefinition cannot be done NOLOGGING.

错误处理:

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('silence','admin_emp',
dbms_redefinition.cons_use_pk);
END;

ORA-12091: cannot online redefine table "SILENCE"."ADMIN_EMP" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 247
ORA-06512: at line 2

SQL> select log_table from user_snapshot_logs;

LOG_TABLE
------------------------------
MLOG$_ADMIN_EMP

SQL> drop snapshot log on ADMIN_EMP;

Materialized view log dropped

SQL> BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('silence','admin_emp',
dbms_redefinition.cons_use_pk);
END;
/

PL/SQL procedure successfully completed


SQL> select mview_name, compile_state from user_mviews;

MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
INT_TEST ERROR

SQL> drop materialized view INT_TEST;

Materialized view dropped

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('silence', 'admin_emp','int_admin_emp',
'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',
dbms_redefinition.cons_use_pk);
END;
/

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

上一篇: 没有了~
下一篇: 内存数据库
请登录后发表评论 登录
全部评论
  • 博文量
    12
  • 访问量
    117771