Oracle在线重定义之DBMS_REDEFINITION

11g官方文档关于DBMS_REDEFINITION的详细说明：

南票网站制作公司哪家好，找创新互联公司！从网页设计、网站建设、微信开发、APP开发、响应式网站设计等网站项目制作，到程序开发，运营维护。创新互联公司从2013年创立到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联公司。

******************************************************************************************************

Using DBMS_REDEFINITION

· Overview

· Constants

· Operational Notes

Overview

To achieve online redefinition, incrementally maintainable local materialized views are used. These logs keep track of the changes to the master tables and are used by the materialized views during refresh synchronization.

为了实现在线重新定义，使用使用本地物化视图。这些日志将跟踪主表的更改，并在刷新同步期间由物化视图使用。

Constants

The DBMS_REDEFINITION package uses the constants shown in Table 110-1, "DBMS_REDEFINITION Constants":

Table 110-1 DBMS_REDEFINITION Constants

Constant	Type	Value	Description
CONS_CONSTRAINT	PLS_INTEGER	3	Used to specify that dependent object type is a constraint
CONS_INDEX	PLS_INTEGER	2	Used to specify that dependent object type is a index
CONS_MVLOG	PLS_INTEGER	10	Used to (un)register a materialized view log, as a dependent object of the table, through theREGISTER_DEPENDENT_OBJECT Procedureand theUNREGISTER_DEPENDENT_OBJECT Procedure.
CONS_ORIG_PARAMS	PLS_INTEGER	1	Used to specify that indexes should be cloned with their original storage parameters
CONS_TRIGGER	PLS_INTEGER	4	Used to specify that dependent object type is a trigger
CONS_USE_PK	BINARY_INTEGER	1	Used to indicate that the redefinition should be done using primary keys or pseudo-primary keys (unique keys with all component columns having not-NULL constraints)
CONS_USE_ROWID	BINARY_INTEGER	2	Used to indicate that the redefinition should be done using rowids

Operational Notes

· CONS_USE_PK and CONS_USE_ROWID are constants used as input to the "options_flag" parameter in both the START_REDEF_TABLE Procedure andCAN_REDEF_TABLE Procedure. CONS_USE_ROWID is used to indicate that the redefinition should be done using rowids while CONS_USE_PK implies that the redefinition should be done using primary keys or pseudo-primary keys (which are unique keys with all component columns having NOT NULL constraints).

CONS_USE_PK和CONS_USE_ROWID是用作START_REDEF_TABLE过程和CAN_REDEF_TABLE过程中“options_flag”参数的输入的常量。 CONS_USE_ROWID用于指示重新定义应使用rowid完成，而CONS_USE_PK意味着重新定义应该使用主键或伪主键（这是所有组件列具有NOT NULL约束的唯一键）来完成。

· CONS_INDEX, CONS_MVLOG,CONS_TRIGGER and CONS_CONSTRAINT are used to specify the type of the dependent object being (un)registered inREGISTER_DEPENDENT_OBJECT Procedure and UNREGISTER_DEPENDENT_OBJECT Procedure (parameter "dep_type").

CONS_INDEX，CONS_MVLOG，CONS_TRIGGER和CONS_CONSTRAINT用于指定在REGISTER_DEPENDENT_OBJECT过程和UNREGISTER_DEPENDENT_OBJECT过程（参数“dep_type”）中注册的依赖对象的类型。

CONS_INDEX ==> dependent object is of type INDEX

CONS_TRIGGER ==> dependent object is of type TRIGGER

CONS_CONSTRAINT==> dependent object type is of type CONSTRAINT

CONS_MVLOG ==> dependent object is of type MATERIALIZED VIEW LOG

· CONS_ORIG_PARAMS as used as input to the "copy_indexes" parameter in COPY_TABLE_DEPENDENTS Procedure. Using this parameter implies that the indexes on the original table be copied onto the interim table using the same storage parameters as that of the original index.

作为COPY_TABLE_DEPENDENTS过程中“copy_indexes”参数的输入的CONS_ORIG_PARAMS。使用此参数意味着使用与原始索引相同的存储参数将原始表上的索引复制到临时表上。

Rules and Limits

For information about various rules and limits that apply to implementation of this package, see the Oracle Database Administrator's Guide.

Summary of DBMS_REDEFINITION Subprograms

Table 110-2 DBMS_REDEFINITION Package Subprograms

Subprogram	Description
ABORT_REDEF_TABLE Procedure	Cleans up errors that occur during the redefinition process and removes all temporary objects created by the reorganization process
CAN_REDEF_TABLE Procedure	Determines if a given table can be redefined online
COPY_TABLE_DEPENDENTS Procedure	Copies the dependent objects of the original table onto the interim table
FINISH_REDEF_TABLE Procedure	Completes the redefinition process.
REGISTER_DEPENDENT_OBJECT Procedure	Registers a dependent object (index, trigger, constraint or materialized view log) on the table being redefined and the corresponding dependent object on the interim table
START_REDEF_TABLE Procedure	Initiates the redefinition process
SYNC_INTERIM_TABLE Procedure	Keeps the interim table synchronized with the original table
UNREGISTER_DEPENDENT_OBJECT Procedure	Unregisters a dependent object (index, trigger, constraint or materialized view log) on the table being redefined and the corresponding dependent object on the interim table

ABORT_REDEF_TABLE Procedure

This procedure cleans up errors that occur during the redefinition process. This procedure can also be used to terminate the redefinition process any time after the START_REDEF_TABLE Procedure has been called and before the FINISH_REDEF_TABLE Procedure is called. This process will remove the temporary objects that are created by the redefinition process such as materialized view logs.

此过程可以清除在重新定义过程中发生的错误。此过程也可以在START_REDEF_TABLE程序被调用之后和调用FINISH_REDEF_TABLE过程之前的任何时候用于终止重新定义过程。此过程将删除由重定义过程创建的临时对象，例如物化视图日志。

Syntax

DBMS_REDEFINITION.ABORT_REDEF_TABLE (

   uname       IN VARCHAR2,

   orig_table  IN VARCHAR2,

   int_table   IN VARCHAR2,

  part_name    IN  VARCHAR2 := NULL);

Parameters

Table 110-3 ABORT_REDEF_TABLE Procedure Parameters

Parameter	Description
uname	Schema name of the tables
orig_table	Name of the table to be redefined
int_table	Name of the interim table
part_name	Name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined.

CAN_REDEF_TABLE Procedure

This procedure determines if a given table can be redefined online. This is the first step of the online redefinition process. If the table is not a candidate for online redefinition, an error message is raised.

此过程确定给定的表是否可以在线重新定义。这是在线重新定义过程的第一步。如果表不是在线重新定义的候选者，则会出现错误消息。

Syntax

DBMS_REDEFINITION.CAN_REDEF_TABLE (

   uname         IN  VARCHAR2,

   tname        IN  VARCHAR2,

   options_flag  IN  PLS_INTEGER := 1,

   part_name     IN  VARCHAR2 := NULL);

Parameters

Table 110-4 CAN_REDEF_TABLE Procedure Parameters

Parameter	Description
uname	Schema name of the table
tname	Name of the table to be re-organized
options_flag	Indicates the type of redefinition method to use. · If dbms_redefinition.cons_use_pk, the redefinition is done using primary keys or pseudo-primary keys (unique keys with all component columns having NOT NULL constraints). The default method of redefinition is using primary keys. · If dbms_redefinition.cons_use_rowid, the redefinition is done using rowids.
part_name	Name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined.

Exceptions

If the table is not a candidate for online redefinition, an error message is raised.

COPY_TABLE_DEPENDENTS Procedure

This procedure clones the dependent objects of the table being redefined onto the interim table and registers the dependent objects. This procedure does not clone the already registered dependent objects.

This subprogram is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table (which represents the post-redefinition table).

此过程克隆表重新定义到临时表上的依赖对象，并注册依赖对象。此过程不会克隆已注册的从属对象。

该子程序用于将重新定义的表中的依赖对象（如授权，触发器，约束和权限）克隆到临时表（表示后重新定义表）。

Syntax

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(

   uname                    IN  VARCHAR2,

   orig_table               IN  VARCHAR2,

   int_table                IN  VARCHAR2,

   copy_indexes             IN  PLS_INTEGER := 1,

   copy_triggers            IN  BOOLEAN     := TRUE,

   copy_constraints         IN  BOOLEAN     := TRUE,

   copy_privileges          IN  BOOLEAN     := TRUE,

   ignore_errors            IN  BOOLEAN     := FALSE,

   num_errors               OUT PLS_INTEGER,

   copy_statistics          IN  BOOLEAN     := FALSE,

   copy_mvlog               IN  BOOLEAN     := FALSE);

Parameters

Table 110-5 COPY_TABLE_DEPENDENTS Procedure Parameters

Parameter	Description
uname	Schema name of the tables
orig_table	Name of the table being redefined
int_table	Name of the interim table
copy_indexes	Flag indicating whether to copy the indexes · 0 - do not copy any index · dbms_redefinition.cons_orig_params – copy the indexes using the physical parameters of the source indexes
copy_triggers	TRUE = clone triggers, FALSE = do nothing
copy_constraints	TRUE = clone constraints, FALSE = do nothing. If compatibility setting is 10.2 or higher, then clone CHECK andNOT NULL constraints
copy_privileges	TRUE = clone privileges, FALSE = do nothing
ignore_errors	TRUE = if an error occurs while cloning a particular dependent object, then skip that object and continue cloning other dependent objects. FALSE = that the cloning process should stop upon encountering an error.
num_errors	Number of errors that occurred while cloning dependent objects
copy_statistics	TRUE = copy statistics, FALSE = do nothing
copy_mvlog	TRUE = copy materialized view log, FALSE = do nothing

Usage Notes

· The user must check the column num_errors before proceeding to ensure that no errors occurred during the cloning of the objects.

· In case of an error, the user should fix the cause of the error and call the COPY_TABLE_DEPENDENTS Procedure again to clone the dependent object. Alternatively the user can manually clone the dependent object and then register the manually cloned dependent object using theREGISTER_DEPENDENT_OBJECT Procedure.

· All cloned referential constraints involving the interim tables will be created disabled (they will be automatically enabled after the redefinition) and all triggers on interim tables will not fire till the redefinition is completed. After the redefinition is complete, the cloned objects will be renamed to the corresponding pre-redefinition names of the objects (from which they were cloned from).

· It is the user's responsibility that the cloned dependent objects are unaffected by the redefinition. All the triggers will be cloned and it is the user's responsibility that the cloned triggers are unaffected by the redefinition.

?用户必须先检查列num_errors，然后继续确保克隆对象期间不会发生错误。

?如果发生错误，用户应该修复错误的原因并再次调用COPY_TABLE_DEPENDENTS过程来克隆依赖对象。或者，用户可以手动克隆依赖对象，然后使用REGISTER_DEPENDENT_OBJECT过程注册手动克隆的依赖对象。

?涉及临时表的所有克隆参照约束将被禁用（重定义后将自动启用），并且临时表上的所有触发器都不会触发，直到重新定义完成。重新定义完成后，克隆的对象将被重命名为对象的重新定义名称（从中克隆到它们）。

?用户有责任克隆的依赖对象不受重新定义的影响。所有的触发器将被克隆，用户有责任克隆的触发器不受重新定义的影响。

FINISH_REDEF_TABLE Procedure

This procedure completes the redefinition process. Before this step, you can create new indexes, triggers, grants, and constraints on the interim table. The referential constraints involving the interim table must be disabled. After completing this step, the original table is redefined with the attributes and data of the interim table. The original table is locked briefly during this procedure.

此过程完成重新定义过程。在此步骤之前，您可以在临时表上创建新的索引，触发器，授予和约束。必须禁用涉及临时表的参照约束。完成此步骤后，原始表格将重新定义为临时表格的属性和数据。在此过程中，原始表被短暂锁定。

Syntax

DBMS_REDEFINITION.FINISH_REDEF_TABLE (

   uname       IN VARCHAR2,

   orig_table  IN VARCHAR2,

   int_table   IN VARCHAR2,

   part_name   IN  VARCHAR2 := NULL);

Parameters

Table 110-6 FINISH_REDEF_TABLE Procedure Parameters

Parameters	Description
uname	Schema name of the tables
orig_table	Name of the table to be redefined
int_table	Name of the interim table
part_name	Name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined.

REGISTER_DEPENDENT_OBJECT Procedure

This procedure registers a dependent object (index, trigger, constraint or materialized view log) on the table being redefined and the corresponding dependent object on the interim table.

此过程在重新定义的表上注册依赖对象（索引，触发器，约束或物化视图日志），并在临时表上注册相应的依赖对象。

This can be used to have the same object on each table but with different attributes. For example: for an index, the storage and tablespace attributes could be different but the columns indexed remain the same

这可以用于在每个表上具有相同的对象，但具有不同的属性。例如：对于索引，存储和表空间属性可能不同，但索引的列保持不变

Syntax

DBMS_REDEFINITION.REGISTER_DEPEPENDENT_OBJECT(

   uname             IN VARCHAR2,

   orig_table        IN VARCHAR2,

   int_table         IN VARCHAR2,

   dep_type          IN  PLS_INTEGER,

   dep_owner         IN VARCHAR2,

   dep_orig_name     IN VARCHAR2,

   dep_int_name      IN  VARCHAR2);

Parameters

Table 110-7 REGISTER_DEPENDENT_OBJECT Procedure Parameters

Parameters	Description
uname	Schema name of the tables
orig_table	Name of the table to be redefined
int_table	Name of the interim table
dep_type	Type of the dependent object (see Constants and Operational Notes)
dep_owner	Owner of the dependent object
dep_orig_name	Name of the original dependent object
dep_int_name	Name of the interim dependent object

Usage Notes

· Attempting to register an already registered object will raise an error.

· Registering a dependent object will automatically remove that object from DBA_REDEFINITION_ERRORS if an entry exists for that object.

?尝试注册已注册的对象会引发错误。

?如果该对象存在条目，则注册依赖对象将自动从DBA_REDEFINITION_ERRORS中删除该对象。

START_REDEF_TABLE Procedure

Prior to calling this procedure, you must manually create an empty interim table (in the same schema as the table to be redefined) with the desired attributes of the post-redefinition table, and then call this procedure to initiate the redefinition.

在调用此过程之前，你必须手动创建一个空的临时表（与要重新定义的表相同的模式）与所需的后重新定义表的属性，然后调用此过程来启动重定义。

Syntax

DBMS_REDEFINITION.START_REDEF_TABLE (

   uname          IN VARCHAR2,

   orig_table     IN VARCHAR2,

   int_table      IN VARCHAR2,

   col_mapping    IN VARCHAR2 := NULL,

   options_flag   IN BINARY_INTEGER := 1,

   orderby_cols   IN VARCHAR2 := NULL,

   part_name     IN  VARCHAR2 := NULL);

Parameters

Table 110-8 START_REDEF_TABLE Procedure Parameters

Parameter	Description
uname	Schema name of the tables
orig_table	Name of the table to be redefined
int_table	Name of the interim table
col_mapping	Mapping information from the columns in the original table to the columns in the interim table. (This is similar to the column list on the SELECT clause of a query.) If NULL, all the columns in the original table are selected and have the same name after redefinition.
options_flag	Indicates the type of redefinition method to use: · If dbms_redefinition.cons_use_pk, the redefinition is done using primary keys or pseudo-primary keys (unique keys with all component columns having NOT NULL constraints). The default method of redefinition is using primary keys. · If dbms_redefinition.cons_use_rowid, the redefinition is done using rowids.
orderby_cols	This optional parameter accepts the list of columns (along with the optional keyword(s) ascending/descending) with which to order by the rows during the initial instantiation of the interim table (the order by is only done for the initial instantiation and not for subsequent synchronizations)
part_name	Name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined.

SYNC_INTERIM_TABLE Procedure

This procedure keeps the interim table synchronized with the original table.

此过程使临时表与原始表同步。

Syntax

DBMS_REDEFINITION.SYNC_INTERIM_TABLE (

   uname          IN  VARCHAR2,

   orig_table     IN  VARCHAR2,

   int_table      IN  VARCHAR2,

   part_name      IN  VARCHAR2 := NULL);

Parameters

Table 110-9 SYNC_INTERIM_TABLE Procedure Parameters

Parameter	Description
uname	Schema name of the table
orig_table	Name of the table to be redefined
int_table	Name of the interim table
part_name	Name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined.

Usage Notes

· This step is useful in minimizing the amount of synchronization needed to be done by the FINISH_REDEF_TABLE Procedure before completing the online redefinition.

· This procedure can be called between long running operations (such as CREATE INDEX) on the interim table to sync it up with the data in the original table and speed up subsequent operations.

?在完成在线重新定义之前，此步骤对于最小化FINISH_REDEF_TABLE过程需要完成的同步量很有用。

?可以在临时表上的长时间运行操作（如CREATE INDEX）之间调用此过程，以将其与原始表中的数据进行同步，并加快后续操作。

UNREGISTER_DEPENDENT_OBJECT Procedure

This procedure unregisters a dependent object (index, trigger, constraint or materialized view log) on the table being redefined and the corresponding dependent object on the interim table.

此过程在重新定义的表上取消注册依赖对象（索引，触发器，约束或物化视图日志），并在临时表上注销相应的依赖对象。

Syntax

DBMS_REDEFINITION.UNREGISTER_DEPEPENDENT_OBJECT(

   uname              IN VARCHAR2,

   orig_table        IN VARCHAR2,

   int_table         IN VARCHAR2,

   dep_type          IN PLS_INTEGER,

   dep_owner         IN VARCHAR2,

   dep_orig_name     IN VARCHAR2,

   dep_int_name      IN  VARCHAR2);

Parameters

Table 110-10 UNREGISTER_DEPENDENT_OBJECT Procedure Parameters

Parameters	Description
uname	Schema name of the tables
orig_table	Name of the table to be redefined
int_table	Name of the interim table
dep_type	Type of the dependent object
dep_owner	Owner of the dependent object
dep_orig_name	Name of the original dependent object
dep_int_name	Name of the interim dependent object

作者：SEian.G（苦练七十二变，笑对八十一难）

网页标题：Oracle在线重定义之DBMS_REDEFINITION
本文URL：http://scyanting.com/article/jigdoh.html

Oracle在线重定义之DBMS_REDEFINITION

Using DBMS_REDEFINITION

Overview

Constants

Operational Notes

Rules and Limits

Summary of DBMS_REDEFINITION Subprograms

ABORT_REDEF_TABLE Procedure

CAN_REDEF_TABLE Procedure

COPY_TABLE_DEPENDENTS Procedure

FINISH_REDEF_TABLE Procedure

REGISTER_DEPENDENT_OBJECT Procedure

START_REDEF_TABLE Procedure

SYNC_INTERIM_TABLE Procedure

UNREGISTER_DEPENDENT_OBJECT Procedure

其他资讯