Oracle線上重定義之COPY_TABLE_DEPENDENTS

語言: CN / TW / HK

theme: vuepress

「這是我參與11月更文挑戰的第26天,活動詳情檢視:2021最後一次更文挑戰

當使用線上重定義功能進行非分割槽錶轉換時,過程中需要對中間表進行索引,約束等依賴進行重建,Oracle提供了兩種方式:

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

一、COPY_TABLE_DEPENDENTS

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

```sql 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

sql 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。

```sql -- 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來複制其他依賴,索引手動建立。

```sql --排除索引 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; ```

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