Oracle線上重定義之DBMS_REDEFINITION

11g官方文件關於DBMS_REDEFINITION的詳細說明：

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

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

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 .

Summary of DBMS_REDEFINITION Subprograms

Table 110-2 DBMS_REDEFINITION Package Subprograms

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

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

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

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

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

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

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

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