Oracle在线定义之COPY_TABLE_DEPENDENTS

网友投稿 568 2022-05-30

当使用在线重定义功能进行非分区表转换时,过程中需要对中间表进行索引,约束等依赖进行重建,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

|

|

Oracle在线重定义之COPY_TABLE_DEPENDENTS

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小时内删除侵权内容。

上一篇:机器人体验营笔记(一)概要
下一篇:Spark Streaming 快速入门系列(6) | DStream的几种保存方式
相关文章