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

In the previous blog post (Online Redefinition online redefinition (1)), we briefly introduced the Oracle online redefinition feature to change the online structure of the data table. This blog post will demonstrate a more complex case, the online redefinition of the complex changes in the case and the role of the key methods of the dbms_redefinition package.

Transformation of general table into partition table

-- create a regular table t_wjq1

SEIANG@seiang11g > create table t_wjq1 as select object_id,object_name,created from dba_objects

Table created.

SEIANG@seiang11g > desc t_wjq1

Name Null? Type

-- -

OBJECT_ID NUMBER

OBJECT_NAME VARCHAR2 (128)

CREATED DATE

SEIANG@seiang11g > select count (*) from t_wjq1

COUNT (*)

-

86997

-- create a primary key on the object_id column of table t_wjq1

SEIANG@seiang11g > alter table t_wjq1 add constraint pk_t_wjq1_id primary key (object_id)

Table altered.

SEIANG@seiang11g >

SEIANG@seiang11g > select index_name,index_type,table_owner,table_name from user_indexes

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME

PK_T_WJQ1_ID NORMAL SEIANG T_WJQ1

Requirements: the contents of the redefinition are as follows:

(1) use object_id for partitioning

(2) the created field has changed from date type to timestamp type.

(3) rename the object_name field to object_name_2.

-- create an intermediate table t_wjq1_interim

SEIANG@seiang11g > create table t_wjq1_interim (

2 object_id number

3 object_name_2 varchar2 (128)

4 created timestamp

5)

6 partition by range (object_id)

7 (

8 partition p1 values less than (5000)

9 partition p2 values less than (10000)

10 partition p3 values less than (50000)

11 partition p4 values less than (maxvalue)

12)

Table created.

-- check the partitioning of the intermediate table

SEIANG@seiang11g > select table_name,partition_name from user_tab_partitions

TABLE_NAME PARTITION_NAME

T_WJQ1_INTERIM P4

T_WJQ1_INTERIM P3

T_WJQ1_INTERIM P2

T_WJQ1_INTERIM P1

First, check to see if the t _ wjq1 table supports redefinition operations

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

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

-- start the redefinition operation

SEIANG@seiang11g > exec dbms_redefinition.start_redef_table ('SEIANG','T_WJQ1','T_WJQ1_INTERIM',col_mapping = >' object_id object_id, object_name object_name_2, to_timestamp (created) created',options_flag = > dbms_redefinition.cons_use_pk)

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.42

Note the col_mapping mapping relationship setting, and if there is a column name translation, explain the column relationship mapping here. If field type conversion is required, write a functional relationship to define the mapping calculation rules.

Oracle online redefinition is based on materialized views. At this point, by looking at the attempt user_mviews, you can see that a new materialized view is generated and there is a corresponding materialized view log.

SEIANG@seiang11g > select mview_name, container_name, query, REFRESH_METHOD from user_mviews

MVIEW_NAME CONTAINER_NAME QUERY REFRESH_

T_WJQ1_INTERIM T_WJQ1_INTERIM select object_id object_id, object_name object_name_2, to_timestamp (created) cre FAST

Elapsed: 00:00:00.02

SEIANG@seiang11g >

SEIANG@seiang11g > select master,log_table from user_mview_logs

MASTER LOG_TABLE

T_WJQ1 MLOG$_T_WJQ1

Elapsed: 00:00:00.00

The online redefined Start method creates a materialized view object t_wjq1_interim in Fast refresh mode. The most important materialized view log in materialized views, named MLOG$_T_WJQ1

-- View the amount of data in the original table and intermediate table

SEIANG@seiang11g > select count (*) from t_wjq1

COUNT (*)

-

86997

Elapsed: 00:00:00.01

SEIANG@seiang11g >

SEIANG@seiang11g > select count (*) from t_wjq1_interim

COUNT (*)

-

86997

Elapsed: 00:00:00.01

-- there is no DML operation, so the materialized view log is still empty

SEIANG@seiang11g > select * from mlog$_t_wjq1

No rows selected

Elapsed: 00:00:00.00

SEIANG@seiang11g > select count (*) from mlog$_t_wjq1

COUNT (*)

-

0

Elapsed: 00:00:00.00

To sum up the above, it shows that the functions of start_redef_table are as follows:

(1) create a materialized view object in Fast refresh mode with the name of Interim data table

(2) load data from the source data table into Interim

(3) create materialized view log

If a DML operation occurs during this process, that is, a DML operation occurs during or after the start process, and new data is inserted or modified, as shown in the following action:

-- View the amount of data in the t_wjq1 table and the maximum object_ id value

SEIANG@seiang11g > select count (*) from t_wjq1

COUNT (*)

-

86997

Elapsed: 00:00:00.00

SEIANG@seiang11g > select max (object_id) from t_wjq1

MAX (OBJECT_ID)

-

89700

Elapsed: 00:00:00.01

-- simulate DML operation: insert database into table t_wjq1

SEIANG@seiang11g > insert into t_wjq1 select object_id+90000,object_name,created from dba_objects

87006 rows created.

Elapsed: 00:00:05.13

-- look again at the changes attempted by the original table t_wjq1, the intermediate table t_wjq1_interim, and the materialized view log

SEIANG@seiang11g > select count (*) from t_wjq1

COUNT (*)

-

174003

Elapsed: 00:00:00.01

SEIANG@seiang11g >

SEIANG@seiang11g > select count (*) from t_wjq1_interim

COUNT (*)

-

86997

Elapsed: 00:00:00.00

SEIANG@seiang11g > select count (*) from mlog$_t_wjq1

COUNT (*)

-

87006

Elapsed: 00:00:00.01

It is found that the data content of the intermediate table remains the same, and the materialized view log accumulates data entries that need to be refreshed. There are inconsistencies and inconsistencies in the data at this time. The Oracle recommendation requires that the change data that occurs during the redefinition be refreshed using the sysnc_interim_table method.

It took more than four minutes to refresh more than 80,000 pieces of data. In this process, we can see the process of refreshing the materialized view.

SEIANG@seiang11g > exec dbms_redefinition.sync_interim_table ('SEIANG','T_WJQ1','T_WJQ1_INTERIM')

PL/SQL procedure successfully completed.

Elapsed: 00:04:18.33

SEIANG@seiang11g > select * from v$mvrefresh

SID SERIAL# CURRMVOWNER CURRMVNAME

-

41 14059 SEIANG T_WJQ1_INTERIM

Refresh start and end process, we can see the time changes in the process of materialized view refresh.

SEIANG@seiang11g > alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

Session altered.

SEIANG@seiang11g >

SEIANG@seiang11g > select name, LAST_REFRESH from user_mview_refresh_times

NAME LAST_REFRESH

T_WJQ1_INTERIM 2017-09-06 13:59:57

SEIANG@seiang11g > select name, LAST_REFRESH from user_mview_refresh_times

NAME LAST_REFRESH

T_WJQ1_INTERIM 2017-09-06 14:22:20

-- after the refresh is completed, changes are found in the data of the tannwjq1 _ interim table and mlog$_t_wjq1 log table

SEIANG@seiang11g > select count (*) from t_wjq1_interim

COUNT (*)

-

174003

SEIANG@seiang11g >

SEIANG@seiang11g > select count (*) from t_wjq1

COUNT (*)

-

174003

SEIANG@seiang11g >

SEIANG@seiang11g > select count (*) from mlog$_t_wjq1

COUNT (*)

-

0

It means that no change in the data needs to be refreshed.

Summing up the above experiments, we know that the essence of the method sync_interim_table is to quickly refresh the materialized view. The duration of this method is determined according to the amount of data and the materialized view refresh algorithm, and does not cause a lot of locking actions in this process. Moreover, this method can be repeated multiple times during the online redefinition process.

-- refresh the constraint relationship in the original data table to the target structure

SEIANG@seiang11g > declare

2 error_count number:=0

3 begin

4 dbms_redefinition.copy_table_dependents (uname = > 'SEIANG',orig_table = >' Thousand WJQ1 recording precinct intact table = > 'Turing WJQ1 inverse INTERIM')

5 copy_indexes = > dbms_redefinition.cons_orig_params

6 num_errors = > error_count)

7 dbms_output.put_line (to_char (error_count))

8 end

9 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.29

SEIANG@seiang11g > select index_name,index_type,table_owner,table_name from user_indexes

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME

TMP$$_PK_T_WJQ1_ID0 NORMAL SEIANG T_WJQ1_INTERIM

PK_T_WJQ1_ID NORMAL SEIANG T_WJQ1

I_MLOG$_T_WJQ1 NORMAL SEIANG MLOG$_T_WJQ1

Elapsed: 00:00:00.04

SEIANG@seiang11g > select master,log_table from user_mview_logs

MASTER LOG_TABLE

T_WJQ1 MLOG$_T_WJQ1

The Finish process mainly completes six steps:

(1) execute the sysnc_interim_table command to keep the intermediate table data as close to the source table as possible

(2) Lock the source data table T so that no changes can occur on the data table afterwards.

(3) execute the sysnc_interim_table command again, and the execution time will not be very long.

(4) replace the names of the source data table and Interim data table

(5) Log out the unregistered materialized view and delete the materialized view log

(6) release the lock on the intermediate table

SEIANG@seiang11g > exec dbms_redefinition.finish_redef_table ('SEIANG','T_WJQ1','T_WJQ1_INTERIM')

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.11

SEIANG@seiang11g > select master,log_table from user_mview_logs

No rows selected

Elapsed: 00:00:00.02

SEIANG@seiang11g >

SEIANG@seiang11g > select * from mlog$_t_wjq1

Select * from mlog$_t_wjq1

*

ERROR at line 1:

ORA-00942: table or view does not exist

SEIANG@seiang11g > select index_name,index_type,table_owner,table_name from user_indexes

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME

TMP$$_PK_T_WJQ1_ID0 NORMAL SEIANG T_WJQ1_INTERIM

PK_T_WJQ1_ID NORMAL SEIANG T_WJQ1

-- check the results of the redefinition

SEIANG@seiang11g > desc t_wjq1

Name Null? Type

-- -

OBJECT_ID NUMBER

OBJECT_NAME_2 VARCHAR2 (128)

CREATED TIMESTAMP (6)

SEIANG@seiang11g >

SEIANG@seiang11g > desc t_wjq1_interim

Name Null? Type

-- -

OBJECT_ID NOT NULL NUMBER

OBJECT_NAME VARCHAR2 (128)

CREATED DATE

SEIANG@seiang11g > exec dbms_stats.gather_table_stats (user,'T_WJQ1',cascade = > true)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.76

-- Partition and primary key objects are implemented successfully

SEIANG@seiang11g > select table_name,partition_name from user_tab_partitions

TABLE_NAME PARTITION_NAME

T_WJQ1 P1

T_WJQ1 P2

T_WJQ1 P3

T_WJQ1 P4

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

CONSTRAINT_NAME C TABLE_NAME

-

TMP$$_PK_T_WJQ1_ID0 P T_WJQ1_INTERIM

PK_T_WJQ1_ID P T_WJQ1

Previous experiments are all online redefinition operations in the case of a single table, but in the actual production environment, it usually involves multi-table relations, such as redefinition under the foreign key relationship table, so how to deal with it? In the next case, the author will introduce online redefinition under multi-table relationships.

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.