---

theme: vuepress

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

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

本文參考：http://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; / ```

用法可參考官方文件：http://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; ```

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