怎么进行Oracle Online Redefinition在线重定义

发布时间:2021-11-06 09:59:11 作者:柒染
来源:亿速云 阅读:138

本篇文章为大家展示了怎么进行Oracle Online Redefinition在线重定义,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。

在之前的文章中,我们看到了如何处理单表在线重定义过程。

4、外键关系表重定义

 

我们先创建出实验数据表。

 

 

SQL> create table t_master as select owner, table_name, tablespace_name, status from dba_tables where 1=0;

Table created

 

SQL> alter table t_master add constraint pk_t_master primary key (owner, table_name);

Table altered

 

Executed in 0.125 seconds

 

SQL> create table t_slave as select owner, table_name, column_name from dba_tab_columns where 1=0;

Table created

 

SQL> alter table T_SLAVE

  2    add constraint pk_t_slave primary key (OWNER, TABLE_NAME, COLUMN_NAME);

Table altered

 

Executed in 0.422 seconds

 

 

T_MASTERT_SLAVE构成主子表关系,插入数据。

 

 

SQL> insert into t_master select owner, table_name, tablespace_name, status from dba_tables;

2841 rows inserted

 

Executed in 0.157 seconds

 

SQL> commit;

Commit complete

 

Executed in 0 seconds

 

SQL> insert into t_slave select owner, table_name, column_name from dba_tab_cols where (owner, table_name) in (select owner, table_name from dba_tables);

32388 rows inserted

 

Executed in 2.328 seconds

 

SQL> commit;

Commit complete

 

Executed in 0 seconds

 

SQL> alter table T_SLAVE

  2    add constraint fk_t_slave_master foreign key (OWNER, TABLE_NAME)

  3    references t_master (OWNER, TABLE_NAME);

Table altered

 

Executed in 0.266 seconds

 

 

创建interim中间表对象,主要目标是将数据表按照owner进行分区,转化为分区表。

 

 

SQL> create table t_master_interim

  2  (owner varchar2(30),

  3   TABLE_NAME      VARCHAR2(30),

  4   TABLESPACE_NAME VARCHAR2(30),

  5   STATUS          VARCHAR2(8)

  6  )

  7  partition by list(owner)

  8  (

  9     partition p1 values ('SYS'),

 10     partition p2 values (default)

 11  )

 12  ;

 

Table created

 

Executed in 0.156 seconds

 

SQL> create table t_slave_interim

  2  (owner varchar2(30),

  3   table_name varchar2(30),

  4   column_name varchar2(30)

  5  )

  6  partition by list(owner)

  7  (

  8     partition p1 values ('SYS'),

  9     partition p2 values (default)

 10  )

 11  ;

 

Table created

 

Executed in 0.032 seconds

 

 

进入正式的重定义流程。这个过程,如果处于安全和顺序关系看,应该是先子表后主表似乎好一点。笔者选择了先主表后子表的方法。

 

 

--判断是否可以进行在线重定义过程;

SQL> set serveroutput on;

SQL> exec dbms_redefinition.can_redef_table('SCOTT','T_MASTER',options_flag => dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed

 

Executed in 0.172 seconds

 

SQL> exec dbms_redefinition.can_redef_table('SCOTT','T_SLAVE',options_flag => dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed

 

Executed in 0.015 seconds

 

 

T_MASTER表进行重定义过程。

 

 

SQL> exec dbms_redefinition.start_redef_table('SCOTT','T_MASTER','T_MASTER_INTERIM',col_mapping => 'owner owner, table_name table_name, tablespace_name tablespace_name, status status',options_flag => dbms_redefinition.cons_use_pk);

 

PL/SQL procedure successfully completed

 

Executed in 1.125 seconds

 

SQL> exec dbms_redefinition.sync_interim_table('SCOTT','T_MASTER','T_MASTER_INTERIM');

PL/SQL procedure successfully completed

 

Executed in 0.047 seconds

 

SQL>

SQL> set serveroutput on;

SQL> declare

  2    error_count number:=0;

  3  begin

  4    dbms_redefinition.copy_table_dependents(uname => 'SCOTT',orig_table => 'T_MASTER',

  5                                            int_table => 'T_MASTER_INTERIM',

  6                                            copy_indexes => dbms_redefinition.cons_orig_params,

  7                                            num_errors => error_count);

  8    dbms_output.put_line(to_char(error_count));

  9  end;

 10  /

 

0

 

PL/SQL procedure successfully completed

Executed in 6.766 seconds

 

SQL> exec dbms_redefinition.finish_redef_table('SCOTT','T_MASTER','T_MASTER_INTERIM');

PL/SQL procedure successfully completed

 

Executed in 1.75 seconds

 

 

进行T_SLAVE表重定义过程。

 

 

SQL> exec dbms_redefinition.start_redef_table('SCOTT','T_SLAVE','T_SLAVE_INTERIM',col_mapping => 'owner owner, table_name table_name, column_name column_name',options_flag => dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed

Executed in 1.484 seconds

 

SQL> exec dbms_redefinition.sync_interim_table('SCOTT','T_SLAVE','T_SLAVE_INTERIM');

PL/SQL procedure successfully completed

Executed in 0.047 seconds

 

SQL>

SQL> set serveroutput on;

SQL> declare

  2    error_count number:=0;

  3  begin

  4    dbms_redefinition.copy_table_dependents(uname => 'SCOTT',orig_table => 'T_SLAVE',

  5                                            int_table => 'T_SLAVE_INTERIM',

  6                                            copy_indexes => dbms_redefinition.cons_orig_params,

  7                                            num_errors => error_count);

  8    dbms_output.put_line(to_char(error_count));

  9  end;

 10  /

 

0

 

PL/SQL procedure successfully completed

Executed in 6.718 seconds

 

SQL> exec dbms_redefinition.finish_redef_table('SCOTT','T_SLAVE','T_SLAVE_INTERIM');

PL/SQL procedure successfully completed

 

Executed in 1.75 seconds

 

 

最后,我们检查处理结果。

 

 

--分区处理成功;

SQL> select table_name, partition_name from dba_tab_partitions where table_owner='SCOTT' and table_name in ('T_MASTER','T_SLAVE');

 

TABLE_NAME                     PARTITION_NAME

------------------------------ ------------------------------

T_MASTER                       P1

T_MASTER                       P2

T_SLAVE                        P1

T_SLAVE                        P2

 

Executed in 0.031 seconds

 

 

约束中存在一些需要额外处理的地方。

 

 

SQL> select constraint_name, constraint_type, R_CONSTRAINT_NAME from dba_constraints where wner='SCOTT' and table_name in ('T_MASTER','T_SLAVE');

 

CONSTRAINT_NAME                CONSTRAINT_TYPE R_CONSTRAINT_NAME

------------------------------ --------------- ------------------------------

TMP$$_FK_T_SLAVE_MASTER0       R               TMP$$_PK_T_MASTER0

SYS_C0011276                   C              

SYS_C0011275                   C              

SYS_C0011274                   C              

PK_T_SLAVE                     P              

FK_T_SLAVE_MASTER              R               PK_T_MASTER

SYS_C0011272                   C              

SYS_C0011271                   C              

PK_T_MASTER                    P              

 

9 rows selected

 

Executed in 0.141 seconds

 

 

由于是分别进行的重定义动作,中间可能有关联裹挟的情况,所以需要额外进行一些处理。主要目标是将Interim数据表删除掉。

 

SQL> drop table t_slave_interim;

Table dropped

 

Executed in 0.438 seconds

 

SQL> alter table t_slave drop constraint "TMP$$_FK_T_SLAVE_MASTER0";

Table altered

 

Executed in 0.031 seconds

 

SQL> drop table t_master_interim purge;

Table dropped

 

Executed in 0.094 seconds

 

 

检查约束情况。

 

 

SQL> select constraint_name, constraint_type, R_CONSTRAINT_NAME from dba_constraints where wner='SCOTT' and table_name in ('T_MASTER','T_SLAVE');

 

CONSTRAINT_NAME                CONSTRAINT_TYPE R_CONSTRAINT_NAME

------------------------------ --------------- ------------------------------

PK_T_MASTER                    P              

PK_T_SLAVE                     P              

FK_T_SLAVE_MASTER              R               PK_T_MASTER

SYS_C0011271                   C              

SYS_C0011272                   C              

SYS_C0011274                   C              

SYS_C0011275                   C              

SYS_C0011276                   C              

 

8 rows selected

 

Executed in 0.125 seconds

 

 

重定义成功。

5、结论和讨论

Oracle在线重定义是一种非常强大的定义工具。这个系列只是介绍了该特性中最常用的一些流程和方法。其他一些诸如register对象和重命名的方法,在一些特定场合下有比较好的使用空间。

应该说,Oracle在线重定义是一种平滑性能、减少锁定、提高系统整体可用性的解决方案。从操作时间和空间消耗上,在线重定义并不具有很高的优势。对于7*24小时的系统,该特性是一种不错的选择。

上述内容就是怎么进行Oracle Online Redefinition在线重定义,你们学到知识或技能了吗?如果还想学到更多技能或者丰富自己的知识储备,欢迎关注亿速云行业资讯频道。

推荐阅读:
  1. oracle_online table redefinition_2
  2. Oracle 12c 能否在2小时内在线完成一张14亿条记录的表结构字段类型变更

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

online redefinition oracle

上一篇:mysql中oak-online-alter-table和pt-online-schema-change的使用限制有哪些

下一篇:MySQL中压测工具Sysbench1.0怎么用

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》