Oracle在線重定義之COPY_TABLE_DEPENDENTS

當使用在線重定義功能進行非分區表轉換時，過程中需要對中間表進行索引，約束等依賴進行重建，oracle提供了兩種方式：

本文參考：https://oracle-base.com/articles/misc/partitioning-an-existing-table

一、COPY_TABLE_DEPENDENTS

使用DBMS_REDEFINITION包自帶的procedure：DBMS_REDEFINITION.copy_table_dependents來實現：

SET SERVEROUTPUT ON DECLARE l_errors NUMBER; BEGIN DBMS_REDEFINITION.copy_table_dependents( uname => USER, orig_table => 'BIG_TABLE', int_table => 'BIG_TABLE2', copy_indexes => DBMS_REDEFINITION.cons_orig_params, copy_triggers => TRUE, copy_constraints => TRUE, copy_privileges => TRUE, ignore_errors => FALSE, num_errors => l_errors, copy_statistics => FALSE, copy_mvlog => FALSE); DBMS_OUTPUT.put_line('Errors=' || l_errors); END; /

用法可參考官方文檔：https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_REDEFINITION.html#GUID-406BDCBD-5EC9-4C27-BA92-AEDFE7853CE7

Table 134-7 COPY_TABLE_DEPENDENTS Procedure Parameters

uname

|

Schema name of the tables

|

|

orig_table

|

Name of the table being redefined

|

|

int_table

|

Name of the interim table

|

|

copy_indexes

|

Flag indicating whether to copy the indexes

0 - do not copy any index

dbms_redefinition.cons_orig_params?– copy the indexes using the physical parameters of the source indexes

|

|

copy_triggers

|

TRUE?= clone triggers,?FALSE?= do nothing

|

|

copy_constraints

|

TRUE?= clone constraints,?FALSE?= do nothing. If compatibility setting is 10.2 or higher, then clone?CHECK?and?NOT?NULL?constraints

|

|

copy_privileges

|

TRUE?= clone privileges,?FALSE?= do nothing

|

|

ignore_errors

|

TRUE?= if an error occurs while cloning a particular dependent object, then skip that object and continue cloning other dependent objects.?FALSE?= that the cloning process should stop upon encountering an error.

|

|

num_errors

|

Number of errors that occurred while cloning dependent objects

|

|

copy_statistics

|

TRUE?= copy statistics,?FALSE?= do nothing

|

|

copy_mvlog

|

TRUE?= copy materialized view log,?FALSE?= do nothing

|

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);

此方式的優缺點：

優點：可以根據你傳入的參數，選擇需要復制的依賴，有索引，觸發器，約束，權限，統計信息。當重定義dbms_redefinition.finish_redef_table之后，會自動切換這些依賴到分區表中，不需要人為rename操作。

缺點：使用此方式復制的索引，會保留非分區表的索引類型，依然是GLOBAL的全局索引，并不會根據分區自動轉換為LOCAL本地索引。

Notes：如果不考慮將索引建為LOCAL本地索引，可以使用如上方式進行復制。

二、手動創建

通過手動創建索引，指定LOCAL本地索引方式創建，但是需要在重定義dbms_redefinition.finish_redef_table之后，手動重新rename。

-- Add new keys, FKs and triggers. ALTER TABLE big_table2 ADD ( CONSTRAINT big_table_pk2 PRIMARY KEY (id) ); CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL; CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL; ALTER TABLE big_table2 ADD ( CONSTRAINT bita_look_fk2 FOREIGN KEY (lookup_id) REFERENCES lookup(id) ); -- Gather statistics on the new table. EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE2', cascade => TRUE); -- Remove original table which now has the name of the interim table. DROP TABLE big_table2; -- Rename all the constraints and indexes to match the original names. ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk; ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk; ALTER INDEX big_table_pk2 RENAME TO big_table_pk; ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i; ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;

優點：可以根據用戶的需求，以適當的方式來創建索引等依賴。

缺點：由于是用戶自己創建并且切換，所以需要有一定的基礎，不能漏掉任何依賴，需要考慮完全。

三、COPY_TABLE_DEPENDENTS + 手動創建索引

也可以通過組合使用，通過COPY_TABLE_DEPENDENTS來復制其他依賴，索引手動創建。

--排除索引 SET SERVEROUTPUT ON DECLARE l_errors NUMBER; BEGIN DBMS_REDEFINITION.copy_table_dependents( uname => USER, orig_table => 'BIG_TABLE', int_table => 'BIG_TABLE2', copy_indexes => 0, copy_triggers => TRUE, copy_constraints => TRUE, copy_privileges => TRUE, ignore_errors => FALSE, num_errors => l_errors, copy_statistics => FALSE, copy_mvlog => FALSE); DBMS_OUTPUT.put_line('Errors=' || l_errors); END; / --創建索引LOCAL（主鍵索引無法創建LOCAL本地索引） CREATE INDEX bita_created_date_i2 ON big_table2(created_date) tablespace USERS LOCAL; CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) tablespace USERS LOCAL; --重定義完成后，rename索引名稱 -- Rename all the constraints and indexes to match the original names. ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i; ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;

優點：綜合上述兩種方式，此方式只需要關注索引是否遺漏，無需關注觸發器，權限，約束等依賴。

Oracle

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。