<a href="http://blog.itpub.net/post/468/12855">http://blog.itpub.net/post/468/12855</a>
在一個高可用系統中，如果需要改變一個表的定義是一件比較棘手的問題，尤其是對于7×24系統。Oracle提供的基本文法基本可以滿足一般性修改，但是對于把普通堆表改為分區表，把索引組織表修改為堆表等操作就無法完成了。而且，對于被大量DML語句通路的表，幸運的是，Oracle從9i版本開始提供了線上重定義表功能，通過調用DBMS_REDEFINITION包，可以在修改表結構的同時允許DML操作。
線上重定義表具有以下功能：
1、修改表的存儲參數；
2、可以将表轉移到其他表空間；
3、增加并行查詢選項；
4、增加或删除分區；
5、重建表以減少碎片；
6、将堆表改為索引組織表或相反的操作；
7、增加或删除一個列。
調用DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色，除此之外，還需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK
ANY TABLE和SELECT ANY TABLE的權限。
線上重定義表的步驟如下：
1.選擇一種重定義方法：
存在兩種重定義方法：一種是基于主鍵；另一種是基于ROWID。ROWID的方式不能用于索引組織表，而且重定義後會存在隐藏列M_ROW$$。預設采用主鍵的方式。
2.調用DBMS_REDEFINITION.CAN_REDEF_TABLE()過程，如果表不滿足重定義的條件，将會報錯并給出原因。
3.在用一個方案中建立一個空的中間表，根據重定義後你期望得到的結建構立中間表。比如：采用分區表，增加了COLUMN等。
4.調用DBMS_REDEFINITION.START_REDEF_TABLE()過程，并提供下列參數：被重定義的表的名稱、中間表的名稱、列的映射規則、重定義方法。
如果映射方法沒有提供，則認為所有包括在中間表中的列用于表的重定義。如果給出了映射方法，則隻考慮映射方法中給出的列。如果沒有給出重定義方法，則認為使用主鍵方式。
5.在中間表上建立觸發器、索引和限制，并進行相應的授權。任何包含中間表的完整性限制應将狀态置為disabled。
當重定義完成時，中間表上建立的觸發器、索引、限制和授權将替換重定義表上的觸發器、索引、限制和授權。中間表上disabled的限制将在重定義表上enable。
6.（可選）如果在執行DBMS_REDEFINITION.START_REDEF_TABLE()過程和執行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程直接在重定義表上執行了大量的DML操作，那麼可以選擇執行一次或多次的SYNC_INTERIM_TABLE()過程，以減少最後一步執行FINISH_REDEF_TABLE()過程時的鎖定時間。
7.執行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程完成表的重定義。這個過程中，原始表會被獨占模式鎖定一小段時間，具體時間和表的資料量有關。
執行完FINISH_REDEF_TABLE()過程後，原始表重定義後具有了中間表的屬性、索引、限制、授權和觸發器。中間表上disabled的限制在原始表上處于enabled狀态。
8.（可選）可以重命名索引、觸發器和限制。對于采用了ROWID方式重定義的表，包括了一個隐含列M_ROW$$。推薦使用下列語句經隐含列置為UNUSED狀态或删除。
ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);
ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;
下面是進行重定義操作後的結果：
原始表根據中間表的屬性和特性進行重定義；
START_REDEF_TABLE()和FINISH_REDEF_TABLE()操作之間在中間表上建立的觸發器、索引、限制和授權，現在定義在原始表上。中間表上disabled的限制在原始表上處于enabled狀态。
原始表上定義的觸發器、索引、限制和授權建立在中間表上，并會在删除中間表時删除。原始表上原來enabled狀态的索引，建立在中間表上，并處于disabled狀态。
任何定義在原始表上的存儲過程和遊标都會變為INVALID，當下次調用時後自動進行編譯。
如果執行過程中出現錯誤或者人為選擇退出的話，可以執行DBMS_REDEFINITION.ABORT_REDEF_TABLE()過程。
<a href="http://hi.baidu.com/edeed/blog/item/ee6811dfb7e5d61b63279832.html">http://hi.baidu.com/edeed/blog/item/ee6811dfb7e5d61b63279832.html</a>
使用Oracle線上重定義包 DBMS_REDEFINITION 在不停業務的情況下增加或修改字段
2009-04-07 15:53
--原表的定義語句, 做線上重定義之前, 記得先把原表定義語句先取出來備用, 可以從toad裡取得.
ALTER TABLE U_TEST.CARD_TEST DROP PRIMARY KEY CASCADE;
DROP TABLE U_TEST.CARD_TEST CASCADE CONSTRAINTS;
CREATE TABLE U_TEST.CARD_TEST
(
CARD_NO VARCHAR2(32 BYTE) NOT NULL,
OPERATE_TIME DATE,
OPERATOR_ID NUMBER(10) DEFAULT 0 NOT NULL
)
TABLESPACE U_TEST_USER01;
CREATE UNIQUE INDEX U_TEST.CARD_TEST_PK ON U_TEST.CARD_TEST (CARD_NO) TABLESPACE U_TEST_USER01;
DROP SYNONYM U_TEST_APP.CARD_TEST;
CREATE SYNONYM U_TEST_APP.CARD_TEST FOR U_TEST.CARD_TEST;
ALTER TABLE U_TEST.CARD_TEST ADD CONSTRAINT CARD_TEST_PK PRIMARY KEY (CARD_NO) USING INDEX TABLESPACE U_TEST_USER01;
GRANT SELECT ON U_TEST.CARD_TEST TO U_TEST_APP;
--做一個臨時存儲過程,模拟不斷在對該表插入資料,因為線上重定義期間要求不影響原有DML操作
grant execute on SYS.DBMS_LOCK to U_TEST;
CREATE OR REPLACE PROCEDURE U_TEST.sp_test
AS
v_temp pls_integer := 0;
BEGIN
FOR i IN 1 .. 1200
LOOP
--注意insert語句裡要有字段清單, 否則重定義期間該過程會有ORA-00947: not enough values錯誤
INSERT INTO U_TEST.CARD_TEST (CARD_NO, OPERATE_TIME, OPERATOR_ID)
VALUES ('tmp' || TO_CHAR (i), SYSDATE, i);
v_temp := v_temp + 1;
COMMIT;
sys.DBMS_LOCK.sleep (1 / 2);
END LOOP;
END;
/
--下面開始重定義, 以dba使用者登陸并開始執行臨時存儲過程
SQL> conn system/xxx
SQL> exec U_TEST.sp_test;
-- 聯機重定義必須考慮的問題: 會占用源表兩倍的空間, 此外還要考慮物化視圖Log的空間以及帶來的其他開銷. 參考http://orafaq.com/node/4
-- 0. 确認正在往表裡插入資料
SQL> select count(*) from U_TEST.CARD_TEST;
-- 1. 确認是否可重定義,如果表不滿足重定義的條件, 将會報錯并給出原因, 預設要求原表有主鍵, 否則得使用rowid标志
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('U_TEST', 'CARD_TEST');
-- 2. 建立一個空的中間表,中間表裡定義好新增字段屬性
SQL> CREATE TABLE U_TEST.CARD_TEST_TMP
CARD_NO VARCHAR2(32 BYTE) NOT NULL,
OPERATOR_ID NUMBER(10) DEFAULT 0 NOT NULL,
GAME_ID NUMBER(3) DEFAULT 1 NOT NULL
-- 3. 開始重定義
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('U_TEST', 'CARD_TEST', 'CARD_TEST_TMP');
-- 此時也可以做修改列名的操作,如:
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('U_TEST', 'CARD_TEST', 'CARD_TEST_TMP', 'CARD_NO CARD_NO, OPERATE_TIME OPERATE_DATE, OPERATOR_ID+100 OPERATOR_ID');
-- 4. 在中間表上建立原表就有的索引,限制,授權,觸發器語句等.
-- 在10g中, 如果這些定義變化了可以通過REGISTER_DEPENDENT_OBJECT()來建立, 否則調用COPY_TABLE_DEPENDENTS()即可.
-- 4.1 定義發生變化的情況下(比如修改了主鍵)
SQL> ALTER TABLE U_TEST.CARD_TEST_TMP ADD CONSTRAINT CARD_TEST_PK1 PRIMARY KEY (CARD_NO, GAME_ID) USING INDEX TABLESPACE U_TEST_index01;
SQL> exec DBMS_REDEFINITION.register_dependent_object('U_TEST','CARD_TEST','CARD_TEST_TMP',
dep_type => DBMS_REDEFINITION.cons_constraint,
dep_owner => 'U_TEST',
dep_orig_name => 'CARD_TEST_PK',
dep_int_name => 'CARD_TEST_PK1');
--4.2 定義未發生變化的情況下(預設把所有索引,限制,授權,觸發器語句都拷貝過來,不包含同義詞,同義詞會一直保留于原表)
DECLARE
retval NUMBER (5);
DBMS_REDEFINITION.copy_table_dependents ('U_TEST',
'CARD_TEST',
'CARD_TEST_TMP',
ignore_errors => TRUE,
num_errors => retval);
DBMS_OUTPUT.put_line (retval);
-- 5. 重定義期間, 原表資料可能發生了變化, 選擇執行一次或多次的SYNC_INTERIM_TABLE()對資料同步, 以減少最後一步執行FINISH_REDEF_TABLE()過程時的鎖定時間.
SQL> EXEC dbms_redefinition.sync_interim_table('U_TEST', 'CARD_TEST', 'CARD_TEST_TMP');
-- 6. 完成表的重定義, 原始表會被獨占模式鎖定一小段時間, 具體時間和表的資料量有關.
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('U_TEST', 'CARD_TEST', 'CARD_TEST_TMP');
-- 此時如果重定義失敗, 通過ABORT_REDEF_TABLE()過程釋放快照, 然後重來
SQL> EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE('U_TEST', 'CARD_TEST', 'CARD_TEST_TMP');
-- 7. 驗證充定義結果
SQL> desc U_TEST.CARD_TEST;
Name Null? Type
--------------------------- -------- --------------------
CARD_NO NOT NULL VARCHAR2(32)
OPERATE_TIME DATE
OPERATOR_ID NOT NULL NUMBER(10)
GAME_ID NOT NULL NUMBER(3)
-- 确認還正常在往表裡插入資料
-- 8. 删除中間表并編譯所有可能失效對象
SQL> drop TABLE U_TEST.CARD_TEST_TMP;
SQL> exec utl_recomp.recomp_serial();
--END--
![阿裡雲天池龍珠計劃SQL訓練營day1[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)