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

This article will explain in detail the example analysis of Oracle Online Redefinition online redefinition. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Starting from a more complex case, we discuss how to do Online Redefinition under complex changes, and the role of the key methods of the dbms_redefinition package.

The redefinition action of a partition table

We define a data table T.

SQL > create table t as select object_id, object_name, created from dba_objects

Table created

SQL > desc t

Name Type Nullable Default Comments

--

OBJECT_ID NUMBER Y

OBJECT_NAME VARCHAR2 (128Y)

CREATED DATE Y

SQL > alter table t add constraint pk_t primary key (object_id)

Table altered

SQL > select count (*) from t

COUNT (*)

-

75192

There are several desired redefinition goals: first, using object_id for partitioning and changing the created field from date type to timestamp type. In addition, the object_name field is renamed to object_name_2. The middle definition table is as follows:

(partition, created variant, object_name field renamed)

SQL > create table t_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 (10000)

9 partition p2 values less than (50000)

10 partition p3 values less than (maxvalue)

11)

12

Table created

First, it is determined whether a redefinition operation can be performed.

SQL > set serveroutput on

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

PL/SQL procedure successfully completed

Start the redefine action.

SQL > exec dbms_redefinition.start_redef_table ('SCOTT','T','T_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

SQL > set timing on

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.

The basis of Oracle online redefinition is materialized view technology. At this point, we check the attempt user_mviews and see that a new materialized view is generated and there is a corresponding materialized view log.

SQL > col query for A20

SQL > select mview_name, container_name, query, REFRESH_METHOD from user_mviews

MVIEW_NAME CONTAINER_NAME QUERY REFRESH_METHOD

--

T_INTERIM T_INTERIM select object_id obj FAST

Ect_id, object_name

Object_name_2, to_ti

Mestamp (created) cre

Ated from "SCOTT". T

"" T "

Executed in 0.031 seconds

SQL > select master, log_table from user_mview_logs

MASTER LOG_TABLE

T MLOG$_T

Executed in 0.016 seconds

The Start method creates a materialized view object t_interim in Fast refresh mode. The most important materialized view log in materialized views is called MLOG$_T.

At this point, the data in the data table is as follows.

-- Source data table and intermediate data table have been synchronized

SQL > select count (*) from t

COUNT (*)

-

75192

Executed in 0.016 seconds

SQL > select count (*) from t_interim

COUNT (*)

-

75192

Executed in 0.031 seconds

-- there is no DML statement procedure, and the materialized view log is still empty.

SQL > select count (*) from mlog$_t

COUNT (*)

-

0

Executed in 0.015 seconds

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

Create a materialized view object in Fast refresh mode with the name of Interim datasheet

Load data into Interim from the source data table

Create materialized view log

If a DML operation occurs during this process, that is, there is a DML operation after and after the start process, and new data is inserted into it.

SQL > select max (object_id) from t

MAX (OBJECT_ID)

-

76847

Executed in 0 seconds

SQL > insert into t select object_id+76847, object_name, created from dba_objects

75199 rows inserted

Executed in 7.297 seconds

SQL > select count (*) from t

COUNT (*)

-

150391

Executed in 0.016 seconds

The data content of the intermediate table remains the same, and the materialized view log accumulates data entries that need to be refreshed.

SQL > select count (*) from t_interim

COUNT (*)

-

75192

Executed in 0.016 seconds

SQL > select count (*) from mlog$_t

COUNT (*)

-

75199

Executed in 0.016 seconds

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.

SQL > exec dbms_redefinition.sync_interim_table ('SCOTT','T','T_INTERIM')

PL/SQL procedure successfully completed

Executed in 195.937 seconds

It took more than three minutes to refresh the data of about 70,000. In this process, we can see the process of refreshing the materialized view.

SQL > select * from v$mvrefresh

SID SERIAL# CURRMVOWNER CURRMVNAME

-

47 13 SCOTT T_INTERIM

During the start and end of the refresh, we can see the time changes during the refresh of the materialized view.

SQL > select name, LAST_REFRESH from user_mview_refresh_times

NAME LAST_REFRESH

--

T_INTERIM 2013-9-10 9:07:01

SQL > select name, LAST_REFRESH from user_mview_refresh_times

NAME LAST_REFRESH

--

T_INTERIM 2013-9-10 9:15:28

After that, we found changes in the data of the interim table and the mlog$_t log table.

SQL > select count (*) from t_interim

COUNT (*)

-

150391

Executed in 0.016 seconds

No change data needs to be refreshed

SQL > select count (*) from mlog$_t

COUNT (*)

-

0

Executed in 0.016 seconds

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.

Next, you need to refresh the constraint relationships in the original data table to the target structure.

SQL > set serveroutput on

SQL > declare

2 error_count number:=0

3 begin

4 dbms_redefinition.copy_table_dependents (uname = > 'SCOTT',orig_table = >' titled focus intact table = > 'titled 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 /

0

PL/SQL procedure successfully completed

The Finish process mainly completes six steps:

Execute the sysnc_interim_table command to bring the intermediate table data as close to the source table as possible

Lock the source data table T so that no changes can occur on this data table afterwards

If you execute the sysnc_interim_table command again, the execution time will not be very long.

Replace the names of the source data table and the Interim data table

Log out the unregistered materialized view and delete the materialized view log

ü release the lock on the intermediate table

SQL > exec dbms_redefinition.finish_redef_table ('SCOTT','T','T_INTERIM')

PL/SQL procedure successfully completed

Executed in 1.953 seconds

SQL > select count (*) from mlog$_t

Select count (*) from mlog$_t

ORA-00942: table or view does not exist

Check the processing results.

According to the original plan, the data table has been changed successfully.

SQL > desc t

Name Type Nullable Default Comments

--

OBJECT_ID NUMBER Y

OBJECT_NAME_2 VARCHAR2 (128Y)

CREATED TIMESTAMP (6) Y

SQL > exec dbms_stats.gather_table_stats (user,'T',cascade = > true)

PL/SQL procedure successfully completed

Executed in 2.719 seconds

The partitioning and primary key objects were implemented successfully.

SQL > select partition_name from user_tab_partitions where table_name='T'

PARTITION_NAME

-

P1

P2

P3

Executed in 0.062 seconds

SQL > select constraint_name, constraint_type from user_constraints where table_name='T'

CONSTRAINT_NAME CONSTRAINT_TYPE

PK_T P

Executed in 0.062 seconds

What we discussed before is the processing in the case of a single table, what if it involves multiple table relationships, such as redefinition under the foreign key relation table?

This is the end of the sample analysis on the online redefinition of Oracle Online Redefinition. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.