【redefinition】在线重定义概览与使用

在线重定义

In any database system, it is occasionally necessary to modify the logical or physical structure of a table to:

• Improve the performance of queries or DML
• Accommodate application changes
• Manage storage

---在以下情况,常需要使用到在线重定义:

l  希望提高查询或dml语句的执行效率

l  适应应用的改变

l  对表的存储属性进行管理

Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.

---oracle数据库提供了在线重定义的方法，使得在不影响表的使用的前提下对表的结构进行修改。与传统的修改方法相比，这种方法增加了可用性

When a table is redefined online, it is accessible to both queries and DML during much of the redefinition process. The table is locked in the exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition, and that is completely transparent to users.

---对一个表做在线重定义的大部分时间里，你可以继续对表进行查询和DML操作。期间，只有在非常小的一段时间内，需要进行锁表，这一小段时间的长短取决于表的大小和复杂度，但这小段时间对用户来讲是完全透明的

Online table redefinition requires an amount of free space that is approximately equivalent to the space used by the table being redefined. More space may be required if new columns are added.

---在线重定义的一个特点是，需要和原表大小差不多的额外空间来完成这个操作。当然，如果你在新定义的表中添加了一些新列，那么就需要更多的空间

Online table redefinition enables you to:

• Modify the storage parameters of a table or cluster
• Move a table or cluster to a different tablespace in the same schema
• Add, modify, or drop one or more columns in a table or cluster
• Add or drop partitioning support (non-clustered tables only)
• Change partition structure
• Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
• Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table
• Add support for parallel queries
• Re-create a table or cluster to reduce fragmentation
• Change the organization of a normal table (heap organized) to an index-organized table, or do the reverse.
• Convert a relational table into a table with object columns, or do the reverse.
• Convert an object table into a relational table or a table with object columns, or do the reverse.

---利用在线重定义，你可以做以下事情：

l  修改表的存储属性

l  将表移动到另一个表空间，但其schema不变

l  添加、修改、删除表中的某个字段

l  对表添加或删除一个分区（聚簇表不支持）

l  改变分区的结构

l  对分区表中单独的一个区进行物理属性的修改，其中就包括将单独的一个区移到其它表空间下

l  增加并行查询选项

l  重建表来减少碎片

l  将普通表转换成索引组织表，或者将索引组织表转换成普通表（堆表）

l  关系表和对象表间的转换

Performing Online Redefinition with DBMS_REDEFINITION

---下面将使用dbms_redefinition包进行在线重定义的过程操作：

1. Choose the redefinition method: by key or by rowed

---在线重定义有两种方法：基于主键或基于rowid

By key—Select a primary key or pseudo-primary key to use for the redefinition. Pseudo-primary keys are unique keys with all component columns having NOT NULL constraints. For this method, the versions of the tables before and after redefinition should have the same primary key columns. This is the preferred and default method of redefinition.

---基于主键：使用主键或伪主键，所谓伪主键就是指有unique、not null约束列的组合。使用这种方法，需要重定义前后的表中有相同的主键列。这是默认的重定义方法

By rowid—Use this method if no key is available. In this method, a hidden column named M_ROW$$ is added to the post-redefined version of the table. It is recommended that this column be dropped or marked as unused after the redefinition is complete. You cannot use this method on index-organized tables.

---基于rowid：这种方法就不需要使用主键列。使用这种方法后，新定义的表中将会有一个隐藏列m_row$$。Oracle建议你将这列删除或标记为不可用。这种方法的一个限制是不能在索引组织表中使用。

2. Verify that the table can be redefined online by invoking the CAN_REDEF_TABLE procedure. If the table is not a candidate for online redefinition, then this procedure raises an error indicating why the table cannot be redefined online.

---在在线重定义开始前，使用can_redef_table包进行验证，确认是否能对表进行在线重定义操作，如果不能，将会返回报错表明原因。

3. Create an empty interim table (in the same schema as the table to be redefined) with all of the desired logical and physical attributes. If columns are to be dropped, do not include them in the definition of the interim table. If a column is to be added, then add the column definition to the interim table. If a column is to be modified, create it in the interim table with the properties that you want.

---然后在同一个用户下创建一个中间表，你可以按照你所希望的表的结构进行创建，并不需要和原表相同，你可以添加新列，删除旧列，改变原有列的属性。

It is not necessary to create the interim table with all the indexes, constraints, grants, and triggers of the table being redefined, because these will be defined in step 6 when you copy dependent objects.

---这一步中，我们可以先不在中间表上创建那些应有的索引、约束和触发器。我们可以第6步中使用另一个包来完成这些操作

4. (Optional) If you are redefining a large table and want to improve the performance of the next step by running it in parallel, issue the following statements:

---(可选)如果你对一个很大的表进行了在线重定义操作，你可以使用下面语句来设置并行操作来加快在线重定义的的过程

alter session force parallel dml parallel degree-of-parallelism;

alter session force parallel query parallel degree-of-parallelism;

 

5. Start the redefinition process by calling START_REDEF_TABLE。

Because this process involves copying data, it may take a while. The table being redefined remains available for queries and DML during the entire process.

---调用dbms_redefinition.start_redef_table()过程，这个过程提供如下参数：被重定义的表的名称，中间表的名称，列的映射规则，重定义方法。如果没有给出列的映射规则，则认为所有包括在中间表中的列用于表的重定义。如果给出了映射规则，则按照映射规则。

因为在此过程中需要进行原始表和中间表间的数据拷贝，所以可能需要较长的时间。但此阶段不会对查询和dml操作产生影响

Note:If START_REDEF_TABLE fails for any reason, you must call ABORT_REDEF_TABLE, otherwise subsequent attempts to redefine the table will fail.

---注释：如果调用dbms_redefinition.start_redefinition_table ()过程失败，不论任何原因，必须调用一次dbms_redefinition.abort_redef_table()，否则后续的操作可能会继续失败

6. Copy dependent objects (such as triggers, indexes, grants, and constraints) and statistics from the table being redefined to the interim table, using one of the following two methods. Method 1 is the preferred method because it is more automatic, but there may be times that you would choose to use method 2. Method 1 also enables you to copy table statistics to the interim table.

---拷贝原始表上的依赖对象（触发器、索引、约束、授权）到中间表上。你可以通过如下两种方法，其中方法1更自动化一些，因为更好一些。方法2是手工方法，但也有些时候更适合使用方法2.

• Method 1: Automatically Creating Dependent Objects

Use the COPY_TABLE_DEPENDENTS procedure to automatically create dependent objects on the interim table. This procedure also registers the dependent objects. Registering the dependent objects enables the identities of these objects and their copied counterparts to be automatically swapped later as part of the redefinition completion process. The result is that when the redefinition is completed, the names of the dependent objects will be the same as the names of the original dependent objects.

---调用dbms_redefinition.copy_table_dependents()过程，自动在中间表上建立原表上有的依赖对象，并且对这些对象进行自动register。Register可以使得中间表上的依赖对象在在线重定义过程中与原始表上的依赖对象进行转换。当在线重定义完成后，中间表上将具有和原表上相同名字的依赖对象。

• Method 2: Manually Creating Dependent Objects

You can manually create dependent objects on the interim table and then register them.

Note: In Oracle Database Release 9i, you were required to manually create the triggers, indexes, grants, and constraints on the interim table, and there may still be situations where you want to or must do so. In such cases, any referential constraints involving the interim table (that is, the interim table is either a parent or a child table of the referential constraint) must be created disabled. When online redefinition completes, the referential constraint is automatically enabled. In addition, until the redefinition process is either completed or aborted, any trigger defined on the interim table does not execute.

---注释：在一些情况下，你需要使用手动创建依赖对象。在在线重定义完成之前，与中间表有关的任何约束都应置为disable状态，完成之后，将自动变为enabled状态。除此之外，在在线重定义完成之前，中间表上的触发器是无效状态。

7.    (Optional) Performing Intermediate Synchronization

After the redefinition process has been started by calling START_REDEF_TABLE and before FINISH_REDEF_TABLE has been called, it is possible that a large number of DML statements have been executed on the original table. If you know that this is the case, it is recommended that you periodically synchronize the interim table with the original table. This is done by calling the SYNC_INTERIM_TABLE procedure. Calling this procedure reduces the time taken byFINISH_REDEF_TABLE to complete the redefinition process. There is no limit to the number of times that you can call SYNC_INTERIM_TABLE.

---（可选）执行中间数据的同步

如果在调用start_redef_table和finishi_redef_table期间，在原始表上进行了大量DML操作，建议执行此步骤，来使得下一步骤8的执行过程更加快一些。此步骤使用dbms_redefinition.sync_interim_table()过程来进行原始表与中间表间的数据同步。在start_redef_table和finishi_redef_table期间，此过程可以多次被执行

The small amount of time that the original table is locked during FINISH_REDEF_TABLE is independent of whether SYNC_INTERIM_TABLE has been called.

---当然，不论此步骤是否被执行，在下一步骤中原始表都将被独占模式锁定一小段时间

8. Execute the FINISH_REDEF_TABLE procedure to complete the redefinition of the table. During this procedure, the original table is locked in exclusive mode for a very short time, independent of the amount of data in the original table. However, FINISH_REDEF_TABLE will wait for all pending DML to commit before completing the redefinition.

---调用dbms_redefinition.finish_redef_table()过程来完成在线重定义。在这个期间，原始表将会被将加X锁，这个过程很短，其依赖于原始表中的数据量。但是，在系统真正执行这个过程之前需要等待已有的dml操作完成commit

9. If you used rowids for the redefinition and your COMPATIBLE initialization parameter is set to 10.1 or lower, set to UNUSED the hidden column M_ROW$$ that is now in the redefined table.

If COMPATIBLE is 10.2 or higher, this hidden column is automatically set to UNUSED for you when redefinition completes.

---如果在前面的操作中，你使用了基于rowid的方法，并且数据库的版本为10.1或更低 。你需要使用下面的语句对隐藏列m_row$$标记为不可用或者将该列删除。如果你的数据库版本为10.2以上，隐藏列会自动标记为不可用，便不需要进行此步操作

ALTER TABLE table_name SET UNUSED (M_ROW$$);

Alter table table_name drop unused columns;

 

下面给出一个在线重定义的范例：

Hr.admin_emp表中有empno,ename,job,deptno四列，对此表按如下要求进行在线重定义操作：

l  表中添加新的列mgr,hiredate,sal,bonus

l  新列bonus的初始值为0,

l  Deptno列在原有的基础上加10

l  重定义后的表是基于empno字段的范围分区表

1.验证admin_emp表是否可以进行基于主键的在线重定义

BEGIN

DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','admin_emp',

DBMS_REDEFINITION.CONS_USE_PK);

END;

/

 

2.根据需求创建中间表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.开始重定义的过程，进行数据的拷贝

BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'admin_emp','int_admin_emp',

       'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',

        dbms_redefinition.cons_use_pk);

END;

/

 

4.在中间表int_admin_emp上创建应有的索引、触发器、约束、授权

DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'admin_emp','int_admin_emp',

   DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);

END;

 

在ignore_errors参数设置为true后，如果发生错误也将跳过忽略，继续执行。例如如果在创建中间表时就在表上建立了主键，则此步骤在向中间表上拷贝原表上的主键时，因有两个主键而报错。但因为ignore_errors参数设置了true后，将会忽略该错误。但是，你也必须执行下一步来确认是否存在其他错误。

5.查询 DBA_REDEFINITION_ERRORS 视图来查看是否有错误发生

SQL> select object_name, base_table_name, ddl_txt from

         DBA_REDEFINITION_ERRORS;

 

OBJECT_NAME   BASE_TABLE_NAME  DDL_TXT

------------- ---------------- ------------------------------

SYS_C005836   ADMIN_EMP        CREATE UNIQUE INDEX "HR"."TMP$

                               $_SYS_C0058360" ON "HR"."INT_A

                               DMIN_EMP" ("EMPNO")

 

SYS_C005836   ADMIN_EMP        ALTER TABLE "HR"."INT_ADMIN_EM

                               P" ADD CONSTRAINT "TMP$$_SYS_C

                               0058360" PRIMARY KEY

 

根据查询结果的错误记录，例子中的错误是因为中间表上已经存在主键所导致。虽然此错误可以忽略，但是如果之前在中间表上已经建立了主键，重新定以后的表上的主键和索引的名字将会发生改变不在是原表上的名字，而是仍旧是中间表上的索引和主键的名字。如果之前不在中间表上建立主键约束，我们便可以避免这个无错发生

最好的方法是在建立中间表时同时建立主键，然后使用register_dependent_object过程来注册主键和索引。然后在使用copy_table_dependents来进行其他依赖对象的拷贝。这种方法既避免了错误的发生，保持中间表上有了主键约束的同时，不会使主键的名字发生改变。

 

如果有其它错误发生，如触发器没有建立成功，可以手动在中间表上建立触发器，然后调用register_dependent_object过程对触发器进行注册。

6.（可选）向int_admin_emp表上进行数据的同步，可以缩短下一步的执行时间

BEGIN

DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'admin_emp', 'int_admin_emp');

END;

/

 

7.完成在线重定义过程

BEGIN

DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'admin_emp', 'int_admin_emp');

END;

/

这个阶段中，admin_emp表会被以独占模式锁定一个很小的时间窗口。这个阶段结束后，原表admin_emp将会被重新定义，其所有属性将会和int_admin_emp相同。（其实是int_admin_emp表上有了amdin_emp的数据，然后两个表从定义上互换名字）

8.此时可以删除掉以int_admin_emp命名的表。

ballontt

2013/4/25

---The End---

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

转载于:http://blog.itpub.net/27425054/viewspace-759290/