【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.

---你也可以手動在中間表上建立這些依賴物件，然後在呼叫dbms_redefinition.register_dependence_object()包進行resiter。

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/，如需轉載，請註明出處，否則將追究法律責任。