阅读 546

Oracle在线重定义之COPY_TABLE_DEPENDENTS

「这是我参与11月更文挑战的第26天,活动详情查看:2021最后一次更文挑战

当使用在线重定义功能进行非分区表转换时,过程中需要对中间表进行索引,约束等依赖进行重建,Oracle提供了两种方式:

本文参考:oracle-base.com/articles/mi…

一、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;
/
复制代码

用法可参考官方文档docs.oracle.com/en/database…

Table 134-7 COPY_TABLE_DEPENDENTS Procedure Parameters

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;
复制代码

优点:综合上述两种方式,此方式只需要关注索引是否遗漏,无需关注触发器,权限,约束等依赖。

文章分类
后端
文章标签