Shulou(Shulou.com)06/01 Report--

In the previous two blog posts, two processes for online redefinition of a single table were listed:

Online Redefinition online redefinition (1): http://blog.itpub.net/31015730/viewspace-2144544/

Online Redefinition online redefinition (2)-- single table complex case: http://blog.itpub.net/31015730/viewspace-2144603/

However, in the production environment, many tables are related to associative relations. In this article, we will take a look at how to redefine multiple associated tables online and transform ordinary tables into partitioned tables.

Requirements: redefine the two tables associated with foreign keys online and transform them into partition tables

First of all, create two experimental data tables t_wjq2_master and t_wjq2_slave.

SEIANG@seiang11g > create table t_wjq2_master as select owner,table_name,tablespace_name,status from dba_tables where 1: 2

Table created.

-- create primary key constraints on the owner and table_name columns of table t_wjq2_master

SEIANG@seiang11g > alter table t_wjq2_master add constraint pk_t_wjq2_master primary key (owner,table_name)

Table altered.

SEIANG@seiang11g > create table t_wjq2_slave as select owner,table_name,column_name from dba_tab_columns where 1: 2

Table created.

-- create primary key constraints on the owner, table_name, and column_name columns of table t_wjq2_slave

SEIANG@seiang11g > alter table t_wjq2_slave add constraint pk_t_wjq2_slave primary key (owner,table_name,column_name)

Table altered.

Insert data into tables t_wjq2_master and t_wjq2_slave, respectively

SEIANG@seiang11g > insert into t_wjq2_master select owner,table_name,tablespace_name,status from dba_tables

2881 rows created.

SEIANG@seiang11g >

SEIANG@seiang11g > insert into t_wjq2_slave select owner,table_name,column_name from dba_tab_columns where (owner,table_name) in (select owner,table_name from dba_tables)

31434 rows created.

-- create a foreign key constraint on t_wjq2_slave. Refer to the taccounwjq2 _ mastertable. Yes, the relationship between the master and child tables of these two tables

SEIANG@seiang11g > alter table t_wjq2_slave add constraint fk_t_wjq2_slave foreign key (owner,table_name) references t_wjq2_master (owner,table_name)

Table altered.

-- View constraints on the table

SEIANG@seiang11g > select owner,constraint_name,constraint_type,table_name from user_constraints

OWNER CONSTRAINT_NAME C TABLE_NAME

-

SEIANG FK_T_WJQ2_SLAVE R T_WJQ2_SLAVE

SEIANG PK_T_WJQ2_SLAVE P T_WJQ2_SLAVE

SEIANG PK_T_WJQ2_MASTER P T_WJQ2_MASTER

SEIANG SYS_C0011655 C T_WJQ2_MASTER

SEIANG SYS_C0011656 C T_WJQ2_MASTER

SEIANG SYS_C0011658 C T_WJQ2_SLAVE

SEIANG SYS_C0011659 C T_WJQ2_SLAVE

SEIANG SYS_C0011660 C T_WJQ2_SLAVE

Create an intermediate table. The main goal is to partition the data table according to owner and convert it into a partitioned table.

SEIANG@seiang11g > create table t_wjq2_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)

Table created.

Elapsed: 00:00:00.02

SEIANG@seiang11g > create table t_wjq2_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)

Table created.

Elapsed: 00:00:00.02

Determine whether the two tables t_wjq2_master and t_wjq2_slave can be redefined online

SEIANG@seiang11g > exec dbms_redefinition.can_redef_table ('SEIANG','T_WJQ2_MASTER',options_flag = > dbms_redefinition.cons_use_pk)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

SEIANG@seiang11g > exec dbms_redefinition.can_redef_table ('SEIANG','T_WJQ2_SLAVE',options_flag = > dbms_redefinition.cons_use_pk)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

Note: from the point of view of security and order, it seems better to put the child table before the main table.

-- start the online redefinition, first redefine the taccounwjq2 _ mastertable

SEIANG@seiang11g > exec dbms_redefinition.start_redef_table ('SEIANG','T_WJQ2_MASTER','T_WJQ2_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.

Elapsed: 00:00:00.98

-- verify that the data has been brushed.

SEIANG@seiang11g > select count (*) from t_wjq2_master

COUNT (*)

-

2881

Elapsed: 00:00:00.00

SEIANG@seiang11g >

SEIANG@seiang11g > select count (*) from t_wjq2_master_interim

COUNT (*)

-

2881

Elapsed: 00:00:00.01

-- perform table synchronization

Note: this step is not necessary, but for larger tables, running incremental synchronization in the middle helps reduce switching time

SEIANG@seiang11g > exec dbms_redefinition.sync_interim_table ('SEIANG','T_WJQ2_MASTER','T_WJQ2_MASTER_INTERIM')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

SEIANG@seiang11g > declare

2 error_count number:=0

3 begin

4 dbms_redefinition.copy_table_dependents (

5 uname = > 'SEIANG',orig_table = >' Tunable WJQ2 master'

6 int_table = > 'Tunable WJQ2MASTERTERENTINTERIM'

7 copy_indexes = > dbms_redefinition.cons_orig_params

8 num_errors = > error_count)

9 end

10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.39

SEIANG@seiang11g > exec dbms_redefinition.finish_redef_table ('SEIANG','T_WJQ2_MASTER','T_WJQ2_MASTER_INTERIM')

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.38

-- the following is an online redefinition of the tdistribuwjq2 _ serve table

SEIANG@seiang11g > exec dbms_redefinition.start_redef_table ('SEIANG','T_WJQ2_SLAVE','T_WJQ2_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.

Elapsed: 00:00:01.31

SEIANG@seiang11g > select count (*) from t_wjq2_slave

COUNT (*)

-

31434

Elapsed: 00:00:00.00

SEIANG@seiang11g >

SEIANG@seiang11g > select count (*) from t_wjq2_slave_interim

COUNT (*)

-

31434

Elapsed: 00:00:00.01

SEIANG@seiang11g > exec dbms_redefinition.sync_interim_table ('SEIANG','T_WJQ2_SLAVE','T_WJQ2_SLAVE_INTERIM')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

SEIANG@seiang11g > declare

2 error_count number:=0

3 begin

4 dbms_redefinition.copy_table_dependents (

5 uname = > 'SEIANG',orig_table = >' Tunable WJQ2 SLAVE'

6 int_table = > 'Tunable WJQ2 SLAVESTERIM'

7 copy_indexes = > dbms_redefinition.cons_orig_params

8 num_errors = > error_count)

9 end

10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.68

SEIANG@seiang11g > exec dbms_redefinition.finish_redef_table ('SEIANG','T_WJQ2_SLAVE','T_WJQ2_SLAVE_INTERIM')

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.60

-- verify the redefinition result and view the partition

SEIANG@seiang11g > select table_name, partition_name from dba_tab_partitions where table_owner='SEIANG' and table_name in ('Tunable WJQ2 MASTERMASTERMASTERMAVE')

TABLE_NAME PARTITION_NAME

T_WJQ2_MASTER P1

T_WJQ2_MASTER P2

T_WJQ2_SLAVE P1

T_WJQ2_SLAVE P2

-- check the status of constraints

SEIANG@seiang11g > select owner,constraint_name,constraint_type,table_name,status from user_constraints where table_name like 'Tunable WJQ2%'

OWNER CONSTRAINT_NAME C TABLE_NAME STATUS

SEIANG SYS_C0011660 C T_WJQ2_SLAVE ENABLED

SEIANG SYS_C0011659 C T_WJQ2_SLAVE ENABLED

SEIANG SYS_C0011658 C T_WJQ2_SLAVE ENABLED

SEIANG TMP$$_SYS_C00116580 C T_WJQ2_SLAVE_INTERIM ENABLED

SEIANG SYS_C0011656 C T_WJQ2_MASTER ENABLED

SEIANG SYS_C0011655 C T_WJQ2_MASTER ENABLED

SEIANG TMP$$_SYS_C00116560 C T_WJQ2_MASTER_INTERIM ENABLED

SEIANG TMP$$_SYS_C00116550 C T_WJQ2_MASTER_INTERIM ENABLED

SEIANG TMP$$_SYS_C00116590 C T_WJQ2_SLAVE_INTERIM ENABLED

SEIANG TMP$$_SYS_C00116600 C T_WJQ2_SLAVE_INTERIM ENABLED

SEIANG FK_T_WJQ2_SLAVE R T_WJQ2_SLAVE ENABLED

SEIANG PK_T_WJQ2_SLAVE P T_WJQ2_SLAVE ENABLED

SEIANG PK_T_WJQ2_MASTER P T_WJQ2_MASTER ENABLED

SEIANG TMP$$_PK_T_WJQ2_MASTER0 P T_WJQ2_MASTER_INTERIM ENABLED

SEIANG TMP$$_PK_T_WJQ2_SLAVE0 P T_WJQ2_SLAVE_INTERIM ENABLED

SEIANG TMP$$_FK_T_WJQ2_SLAVE0 R T_WJQ2_SLAVE DISABLED

SEIANG TMP$$_FK_T_WJQ2_SLAVE1 R T_WJQ2_SLAVE_INTERIM DISABLED

SEIANG TMP$$_TMP$$_FK_T_WJQ2_SLAVE0 R T_WJQ2_SLAVE_INTERIM DISABLED

Redefined successfully.

Oracle online redefinition is a very powerful definition tool. Some of the most commonly used processes and methods of online redefinition are introduced through several simple cases of three articles. Other methods, such as register objects and renaming methods, have better room for use in certain situations. For detailed usage, please refer to another blog post: DBMS_REDEFINITION: http://blog.itpub.net/31015730/viewspace-2144516/ of Oracle online redefinition.

When using online redefinitions, you need to be aware of the following:

1. If offline operation can solve the problem, do not use online redefinition. For example, some static data and historical data can be archived and migrated using CTAS, alter table move … , or export and import complete

2. The table space should have at least more remaining space than the space used by the source table.

3. the operation process of online redefinition takes a long time, but has the least impact on the business.

4. Pay attention to the transaction operations on the source table. If it is too frequent, a serious wait may occur.

It should be said that Oracle online redefinition is a solution to smooth performance, reduce locking, and improve the overall availability of the system. In terms of operating time and space consumption, online redefinition does not have a high advantage. This feature is a good choice for 7-hour, 24-hour systems.

Reference link:

Http://blog.itpub.net/11676357/viewspace-1052296/

Http://www.cnblogs.com/flowerszhong/p/4535206.html

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.