Oracle 在線重定義（Online Redefinition） 是一種功能，通過DBMS_REDEFINITION 包提供，允許DBA在不需要停止或顯著影響數據庫正常操作的情況下，對數據庫表進行結構化修改。
可以實現的功能
將表移動到其它表空間
增加、修改或者刪除表的字段
將非分區表轉換為分區表
修改表的分區結構
高水位線回收
將普通表轉換為索引組織表
測試數據
五千萬條數據，數據文件test_tbs01.dbf,表空間test_tbs
將表移動到其它表空間
#創建新表空間
CREATE?TABLESPACE?new_tbs?DATAFILE?'/datafile/new_tbs01.dbf'?SIZE?10G?AUTOEXTEND?ON?NEXT?1G?MAXSIZE?UNLIMITED;#給用戶授權
ALTER?USER?TEST_USER?QUOTA?UNLIMITED?ON?NEW_TBS;#檢查選擇的表是否可以執行在線重定義
BEGINDBMS_REDEFINITION.CAN_REDEF_TABLE('TEST_USER',?'TEST_TABLE');
END;
/#創建中間表（結構與原表一致，但指定新表空間）
CREATE?TABLE?test_user.test_table_int?(id?NUMBER?PRIMARY?KEY,name?VARCHAR2(100)?NOT?NULL,created_date?DATE,value?NUMBER(10),comments?VARCHAR2(500)
)?TABLESPACE?new_tbs;#?開始在線重定義（使用主鍵方式）
BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT',options_flag?=>?DBMS_REDEFINITION.CONS_USE_PK??--?使用主鍵);
END;
/#?復制依賴對象（自動復制索引、約束）
DECLAREnum_errors?PLS_INTEGER;
BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT',copy_indexes?=>?DBMS_REDEFINITION.CONS_ORIG_PARAMS,copy_triggers?=>?TRUE,copy_constraints?=>?TRUE,copy_privileges?=>?TRUE,ignore_errors?=>?TRUE,num_errors?=>?num_errors);
END;
/#?同步數據
BEGINDBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT');
END;
/#?完成重定義（短暫鎖表）#?DBMS_REDEFINITION.FINISH_REDEF_TABLE?會執行flush?shard?pool?需要規避
alter?session?set?events?'10995?trace?name?context?forever,?level?2';BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT');
END;
/
增加、修改或者刪除表的字段
操作：添加字段new_column VARCHAR2(50)，刪除字段comments。
#檢查選擇的表是否可以執行在線重定義
BEGINDBMS_REDEFINITION.CAN_REDEF_TABLE('TEST_USER',?'TEST_TABLE');
END;
/
#?創建中間表（添加新字段，刪除舊字段）
CREATE?TABLE?test_user.test_table_int?(id?NUMBER?PRIMARY?KEY,name?VARCHAR2(100)?NOT?NULL,created_date?DATE,value?NUMBER(10),new_column?VARCHAR2(50)
)?TABLESPACE?test_tbs;
#?開始在線重定義（使用主鍵方式）
BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname????????=>?'TEST_USER',?orig_table???=>?'TEST_TABLE',int_table????=>?'TEST_TABLE_INT',col_mapping??=>?'id?id,?name?name,?created_date?created_date,?value?value',?options_flag?=>?DBMS_REDEFINITION.CONS_USE_PK);
END;
/
#?復制依賴對象（自動復制索引、約束）
DECLAREnum_errors?PLS_INTEGER;
BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT',copy_indexes?=>?DBMS_REDEFINITION.CONS_ORIG_PARAMS,copy_triggers?=>?TRUE,copy_constraints?=>?TRUE,copy_privileges?=>?TRUE,ignore_errors?=>?TRUE,num_errors?=>?num_errors);
END;
/
#?同步數據
BEGINDBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT');
END;
/
#?完成重定義
BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT');
END;
/
將非分區表轉換為分區表
#檢查選擇的表是否可以執行在線重定義
BEGINDBMS_REDEFINITION.CAN_REDEF_TABLE('TEST_USER',?'TEST_TABLE');
END;
/
#創建中間表
CREATE?TABLE?test_user.test_table_int?(id????????????NUMBER,name??????????VARCHAR2(100)?NOT?NULL,created_date??DATE?NOT?NULL,?value?????????NUMBER(10),new_column????VARCHAR2(50),CONSTRAINT?pk_test_table_int?PRIMARY?KEY?(id,?created_date)?
)
PARTITION?BY?RANGE?(created_date)?(PARTITION?p1?VALUES?LESS?THAN?(TO_DATE('2023-01-01',?'YYYY-MM-DD')),PARTITION?p2?VALUES?LESS?THAN?(TO_DATE('2024-01-01',?'YYYY-MM-DD')),PARTITION?p3?VALUES?LESS?THAN?(MAXVALUE)
)?TABLESPACE?test_tbs;
#?開始在線重定義（使用主鍵方式）
BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname????????=>?'TEST_USER',orig_table???=>?'TEST_TABLE',int_table????=>?'TEST_TABLE_INT',col_mapping??=>?'id?id,?name?name,?created_date?created_date,?value?value,?new_column?new_column',??options_flag?=>?DBMS_REDEFINITION.CONS_USE_PK);
END;
/
--?復制依賴對象（自動復制索引、約束）
DECLAREnum_errors?PLS_INTEGER;
BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT',copy_indexes?=>?DBMS_REDEFINITION.CONS_ORIG_PARAMS,copy_triggers?=>?TRUE,copy_constraints?=>?TRUE,copy_privileges?=>?TRUE,ignore_errors?=>?TRUE,num_errors?=>?num_errors);
END;
/
#?同步數據
BEGINDBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT');
END;
/
#?完成重定義#?DBMS_REDEFINITION.FINISH_REDEF_TABLE?會執行flush?shard?pool?需要規避
alter?session?set?events?'10995?trace?name?context?forever,?level?2';BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT');
END;
/
#創建新表空間
CREATE?TABLESPACE?new_tbs?DATAFILE?'/datafile/new_tbs01.dbf'?SIZE?10G?AUTOEXTEND?ON?NEXT?1G?MAXSIZE?UNLIMITED;#給用戶授權
ALTER?USER?TEST_USER?QUOTA?UNLIMITED?ON?NEW_TBS;#檢查選擇的表是否可以執行在線重定義
BEGINDBMS_REDEFINITION.CAN_REDEF_TABLE('TEST_USER',?'TEST_TABLE');
END;
/#創建中間表（結構與原表一致，但指定新表空間）
CREATE?TABLE?test_user.test_table_int?(id?NUMBER?PRIMARY?KEY,name?VARCHAR2(100)?NOT?NULL,created_date?DATE,value?NUMBER(10),comments?VARCHAR2(500)
)?TABLESPACE?new_tbs;#?開始在線重定義（使用主鍵方式）
BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT',options_flag?=>?DBMS_REDEFINITION.CONS_USE_PK??--?使用主鍵);
END;
/--?復制依賴對象（自動復制索引、約束）
DECLAREnum_errors?PLS_INTEGER;
BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT',copy_indexes?=>?DBMS_REDEFINITION.CONS_ORIG_PARAMS,copy_triggers?=>?TRUE,copy_constraints?=>?TRUE,copy_privileges?=>?TRUE,ignore_errors?=>?TRUE,num_errors?=>?num_errors);
END;
/#?同步數據
BEGINDBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT');
END;
/#?完成重定義（短暫鎖表）#?DBMS_REDEFINITION.FINISH_REDEF_TABLE?會執行flush?shard?pool?需要規避
alter?session?set?events?'10995?trace?name?context?forever,?level?2';BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT');
END;
/
修改表的分區結構
當前表為范圍分區：
#?創建哈希分區中間表CREATE?TABLE?test_user.test_table_int?(id????????????NUMBER,name??????????VARCHAR2(100)?NOT?NULL,created_date??DATE,value?????????NUMBER(10),new_column????VARCHAR2(50),CONSTRAINT?pk_test_table_int?PRIMARY?KEY?(id)
)
PARTITION?BY?HASH?(id)
PARTITIONS?4
TABLESPACE?test_tbs;
#?啟動在線重定義
BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname????????=>?'TEST_USER',orig_table???=>?'TEST_TABLE',int_table????=>?'TEST_TABLE_INT',col_mapping??=>?'id?id,?name?name,?created_date?created_date,?value?value,?new_column?new_column',options_flag?=>?DBMS_REDEFINITION.CONS_USE_PK);
END;
/
#?復制依賴對象（自動復制索引、觸發器等）
DECLAREnum_errors?PLS_INTEGER;
BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname??????????=>?'TEST_USER',orig_table?????=>?'TEST_TABLE',int_table??????=>?'TEST_TABLE_INT',copy_indexes???=>?DBMS_REDEFINITION.CONS_ORIG_PARAMS,copy_triggers??=>?TRUE,ignore_errors??=>?TRUE,num_errors?????=>?num_errors);DBMS_OUTPUT.PUT_LINE('依賴對象錯誤:?'?||?num_errors);
END;
/
#?同步數據
BEGINDBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname????????=>?'TEST_USER',orig_table???=>?'TEST_TABLE',int_table????=>?'TEST_TABLE_INT');
END;
/
#?完成重定義（短暫鎖表）
BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE(uname????????=>?'TEST_USER',orig_table???=>?'TEST_TABLE',int_table????=>?'TEST_TABLE_INT');
END;
/
高水位線回收（碎片整理）
#?記錄當前高水位線
SELECT?blocks,?empty_blocks?
FROM?dba_tables?
WHERE?owner?=?'TEST_USER'?AND?table_name?=?'TEST_TABLE';
#檢查選擇的表是否可以執行在線重定義
BEGINDBMS_REDEFINITION.CAN_REDEF_TABLE('TEST_USER',?'TEST_TABLE');
END;
/
#創建中間表
CREATE?TABLE?test_user.test_table_int?(id?NUMBER?PRIMARY?KEY,name?VARCHAR2(100)?NOT?NULL,created_date?DATE,value?NUMBER(10),new_column?VARCHAR2(50)
)?TABLESPACE?test_tbs;
#?開始在線重定義（使用主鍵方式）
BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname????????=>?'TEST_USER',orig_table???=>?'TEST_TABLE',int_table????=>?'TEST_TABLE_INT',col_mapping??=>?'id?id,?name?name,?created_date?created_date,?value?value,?new_column?new_column',??options_flag?=>?DBMS_REDEFINITION.CONS_USE_PK);
END;
/
--?復制依賴對象（自動復制索引、約束）
DECLAREnum_errors?PLS_INTEGER;
BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT',copy_indexes?=>?DBMS_REDEFINITION.CONS_ORIG_PARAMS,copy_triggers?=>?TRUE,copy_constraints?=>?TRUE,copy_privileges?=>?TRUE,ignore_errors?=>?TRUE,num_errors?=>?num_errors);
END;
/
#?同步數據
BEGINDBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT');
END;
/
#?完成重定義#?DBMS_REDEFINITION.FINISH_REDEF_TABLE?會執行flush?shard?pool?需要規避
alter?session?set?events?'10995?trace?name?context?forever,?level?2';BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE(uname?=>?'TEST_USER',orig_table?=>?'TEST_TABLE',int_table?=>?'TEST_TABLE_INT');
END;
/
檢查高水位是否回收
SELECT?blocks,?empty_blocks?
FROM?dba_tables?
WHERE?owner?=?'TEST_USER'?AND?table_name?=?'TEST_TABLE';
轉換為索引組織表（IOT）
總體步驟都與前面相同
