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

This article shows you how to redefine Oracle Online Redefinition online. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can gain something through the detailed introduction of this article.

In the previous article, we saw how to handle the single table online redefinition process.

4. Redefinition of foreign key relation table

Let's first create an experimental data table.

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_MASTER and T_SLAVE form a master child table relationship and insert data.

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

The main goal of creating an interim intermediate table object is to partition the data table according to owner and transform it into a partitioned table.

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

Enter the formal redefinition process. This process, in terms of security and order, should be better than the child table before the master table. The author chooses the method of first main table and then child table.

-to determine whether the online redefinition process can be carried out

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

The TASTER table performs the redefinition process.

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 = >' titled master')

5 int_table = > 'titled master intermediate'

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

Perform the T_SLAVE redefinition process.

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 = >' Tunable SLAVE')

5 int_table = > 'Tunable SLAVEINTERIM'

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

Finally, we check the processing results.

-- Partition processing succeeded

SQL > select table_name, partition_name from dba_tab_partitions where table_owner='SCOTT' and table_name in.

TABLE_NAME PARTITION_NAME

T_MASTER P1

T_MASTER P2

T_SLAVE P1

T_SLAVE P2

Executed in 0.031 seconds

There are some additional areas in the constraint that need to be dealt with.

SQL > select constraint_name, constraint_type, R_CONSTRAINT_NAME from dba_constraints where wner='SCOTT' and table_name in.

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

Because it is a separate redefinition action, there may be associated entanglement, so some additional processing is needed. The main goal is to delete the Interim data table.

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

Check for constraints.

SQL > select constraint_name, constraint_type, R_CONSTRAINT_NAME from dba_constraints where wner='SCOTT' and table_name in.

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

Redefined successfully.

5. Conclusion and discussion

Oracle online redefinition is a very powerful definition tool. This series only introduces some of the most commonly used processes and methods in this feature. Other methods, such as register objects and renaming methods, have better room for use in certain situations.

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.

The above is how to redefine Oracle Online Redefinition online. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.