Seamlessly Redefine Oracle Tables in Production with DBMS

Online table redefinition 允許您在 production 中重組 Oracle 表,而不使數據不可用。您可能习惯使用 temp tables 移動數據,但有更好的解決方案。

Introduction

Staging data 和移動它來重組表會使表和數據在一段時間內不可用,這對業務不利。這就是 DBMS_REDEFINITION 套件拯救局面的時候,如下圖所示:

Seamlessly Redefine Oracle Tables in Production with DBMS_REDEFINITION

Purpose

定期需要修改 Oracle 表的邏輯或物理結構,原因如下:

增強查詢或 Data Manipulation Language (DML) 性能
適應應用程序變更
管理存儲

Oracle Database 提供一種機制,可以在不顯著影響表可用性的情況下修改表結構,稱為 online table redefinition。與傳統方法相比,online redefinition 提供了巨大的性能提升。

當表在線重新定義時,它在重新定義過程的大部分時間內對查詢和 DML 都可訪問。表僅在短時間內以獨佔模式鎖定,這與表的大小和重新定義的複雜性無關。重新定義過程對用戶完全透明。

Online table redefinition 需要大約等於當前表所用空間的免費空間。

重組表有許多方法。當停機時間是挑戰時,DBMS_REDEFINITION 套件是最佳選擇。

Redefine a Table Online

選擇重新定義方法:by key 或 by rowids。

By key: 選擇 primary key 或 pseudo-primary key 用於重新定義。Pseudo-primary keys 是所有組件列都包含 NOT NULL 約束的唯一鍵。對於此方法,重新定義前後的表版本應包含相同的 primary key columns。這是首選且默認的方法。

By rowid: 如果沒有可用的鍵,請使用此方法。在此方法中,一個名為 M_ROW$$ 的隱藏列將被添加到重新定義後的表版本中。重新定義完成後,應刪除或標記為 unused。如果 COMPATIBLE 設置為 10.2.0 或更高,重新定義的最終階段會自動將此列設置為 UNUSED。然後可以使用 ALTER TABLE ... DROP UNUSED COLUMNS 語句刪除它。不能在 index-organized tables 上使用此方法。
通過調用 CAN_REDEF_TABLE 過程驗證表是否可以在線重新定義。如果表不是 online redefinition 的候選者,則此過程會引發錯誤,指示表為什麼不能在線重新定義。
創建一個空的 interim table (在與要重新定義的表相同的 schema 中),具有所有期望的邏輯和物理屬性。
不必使用要重新定義的表的所有 indexes, constraints, grants, 和 triggers 創建 interim table。這可以通過使用 COPY_TABLE_DEPENDENTS 過程自動完成。

為了提高大表的性能,可以通過以下命令設置並行:

ALTER SESSION force parallel dml parallel degree-of-parallelism;
ALTER SESSION force parallel query parallel degree-of-parallelism;

FINISH_REDEF_TABLE 命令完成表的重新定義。在此過程中,原始表將在短時間內以獨佔模式鎖定,與原始表中的數據量無關。但是,FINISH_REDEF_TABLE 會等待所有掛起的 DML 操作提交,然後完成重新定義。
如果您使用 rowids 進行重新定義,且 COMPATIBLE 初始化參數設置為 10.1.0 或更低,則需要刪除添加到重新定義表中的隱藏列 M_ROW$$。也可以通過以下命令將該列設置為「UNUSED」:
```
ALTER TABLE <table_name> SET UNUSED (M_ROW$$);
```
如果 COMPATIBLE 是 10.2.0 或更高,此隱藏列在重新定義完成時自動設置為 UNUSED。然後可以用 ALTER TABLE ... DROP UNUSED COLUMNS 語句刪除。等待針對 interim table 的任何長時間運行查詢完成,然後刪除 interim table。

Sample Table Redefinition

以下部分顯示示例表重新定義的各種命令和輸出。

Start sqlplus

[oracle@vm215 ~]$ sqlplus amit/amit
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 29 05:44:44 2016
...
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Create a Demo Table

SQL> CREATE TABLE TEST1 (
    ID NUMBER(10),
    ENAME VARCHAR2(10),
    SAL NUMBER(10)
);

Insert Bulk Rows

SQL> INSERT INTO AMIT.TEST1 SELECT ROWNUM, 'T'|| ROWNUM, DBMS_RANDOM.VALUE(100000, 999999) FROM DUAL CONNECT BY LEVEL < 1000000;
999999 ROWS CREATED.
SQL> COMMIT;

Create Dependent Objects for Testing

View creation

SQL> CREATE VIEW TEST1_VW AS SELECT * FROM TEST1;

Sequence creation

SQL> CREATE SEQUENCE TEST_SEQ;

Procedure creation

CREATE OR REPLACE PROCEDURE PROC1 (P_ID IN NUMBER) AS
    V_ID NUMBER;
BEGIN
    SELECT SAL INTO V_ID FROM TEST1 WHERE ID = P_ID;
END;
/
PROCEDURE CREATED.

DML trigger creation

SQL> CREATE OR REPLACE TRIGGER AMIT_TRIG BEFORE INSERT OR UPDATE ON TEST1 FOR EACH ROW
DECLARE X NUMBER;
BEGIN
    SELECT COUNT(*) INTO X FROM TEST1 WHERE ID = :NEW.ID;
    IF X > 0 THEN RAISE_APPLICATION_ERROR(-20501, 'ID' || :NEW.ID || ' ALREADY EXISTS');
END IF;
END;
/
TRIGGER CREATED.

Primary key creation

SQL> ALTER TABLE TEST1 ADD CONSTRAINT TEST1_ID_PK PRIMARY KEY (ID);

Get the status before redefinition

SQL> COLUMN OBJECT_NAME FORMAT A20
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS ORDER BY OBJECT_NAME;
OBJECT_NAME     OBJECT_TYPE     STATUS
----------------  --------------  -------
AMIT_TRIG        TRIGGER         VALID
PROC1            PROCEDURE       VALID
TEST1            TABLE           VALID
TEST1_ID_PK      INDEX           VALID
TEST1_VW         VIEW            VALID
TEST_SEQ         SEQUENCE        VALID
6 ROWS SELECTED.

Check Table for Redefinition

Using primary key

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

Using rowid

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

Create a Replica of the Interim Table

SQL> CREATE TABLE TEST1_REORG AS SELECT * FROM TEST1 WHERE ROWNUM=5;
TABLE CREATED.
SQL> SELECT COUNT(*) FROM TEST1_REORG;  -- 0
SQL> SELECT COUNT(*) FROM TEST1;       -- 999999

Connect to Database

[oracle@vm215 ~]$ sqlplus / as sysdba
Connected to: Oracle database 11g enterprise edition release 11.2.0.3.0 - 64bit production

Start Redefinition

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('AMIT','TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

Copy Dependent Objects

SQL> DECLARE N PLS_INTEGER;
BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('AMIT', 'TEST1','TEST1_REORG',
        DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, N);
END;
/
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

Check for Errors

SQL> COL OBJECT_NAME FOR A25 SET LIN200 PAGES 200 COL DDL_TEXT FOR A60
SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM DBA_REDEFINITION_ERRORS;
NO ROWS SELECTED

Validate Both Tables

SQL> SELECT COUNT(*) FROM AMIT.TEST1_REORG;  -- 999999
SQL> SELECT COUNT(*) FROM AMIT.TEST1;      -- 999999
SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('AMIT', 'TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

Finish the Redefinition

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('AMIT', 'TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='AMIT';
-- Shows some INVALID triggers, need recompile

Check Error and Recompile the Schema

SQL> EXEC UTL_RECOMP.RECOMP_SERIAL('AMIT');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='AMIT';
-- All VALID now

Drop the Interim Table

SQL> DROP TABLE AMIT.TEST1_REORG;
TABLE DROPPED.

Conclusion

如果表結構需要修改,同時被終端用戶訪問,請使用 DBMS_REDEFINITION。此功能有助於在無停機時間的情況下重組數據,從而避免在線交易處理 (OLTP) 環境中由停機時間引起的挑戰。

Seamlessly Redefine Oracle Tables in Production with DBMS_REDEFINITION