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

Oracle Online Redefinition online redefinition is what, in view of this question, this article introduces in detail the corresponding analysis and solutions, hoping to help more partners who want to solve this problem to find a more simple and easy way.

In the face of more and more 724 systems, the time window available for operation and maintenance personnel to work is becoming smaller and smaller. In the limited time window, hardware maintenance and network transformation configuration occupy a lot of time. The daily maintenance of database objects has increasingly become a problem that we need to pay attention to.

Operations such as data rearrangement, table partition, field type modification, field addition and modification are relatively easy to carry out in the development and test environment. Even if the datasheet is large and the operation can be time-consuming, we can win the operation time window by some non-technical means. However, for the production system, the long-term locking during the operation may not be acceptable to the business. At this point, you can consider some of the Online operation techniques of Oracle.

In some previous articles, the author introduced some online processing methods, such as deleting massive data tables, temporarily hiding unused specific data columns, optimizing default value columns in 11g, and so on. We introduce the Online Redefinition (online redefinition) feature of Oracle.

1. Basic knowledge

Oracle Online Redefinition can guarantee that when the data table carries out DDL type operations, such as inserting and deleting data columns, and partitioning, it can also support DML operations, especially insert/update/delete operations.

For normal DDL processes, Oracle gives data objects an exclusive table lock. In other words, during the DDL operation, we cannot DML the data table (add, modify and delete operations). You must wait for the DDL to finish before you can continue.

In other words, if a DDL lasts for a long time, such as the massive size of the data table, then the system data table can not provide services for a long time.

Oracle online redefinition provides a way to solve the problem. If we need to redefine a data table, we need to define an intermediate target table, Interim. Define the target structure in the Interim table, such as desired storage, partitions, and field relationships. Online redefinition mainly uses the corresponding method of dbms_redefinition package.

The biggest feature of online redefinition is that when performing online operations, we can also perform DML operations on data tables. At the end of the definition process, all operations performed during the period can be fed back to the target data table.

Currently, Oracle online redefinition supports the following types of redefinition actions:

Insert, delete and rename an existing datasheet column

ü modify field type

Elimination of fragments in data table segments

Redefinition of index, constraint, etc.

U partition table transformation

Next, let's take a look at how to redefine using the dbms_redefinition package through a simple example.

2. Simple redefinition example

We carried out the test experiment in Oracle 11gR2.

SQL > select * from v$version where rownum create table t as select object_id, object_name, owner from dba_objects

Table created

Executed in 0.328 seconds

SQL > select count (*) from t

COUNT (*)

-

75192

Executed in 0.047 seconds

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

Table altered

The first step in online redefinition is to determine whether the target data table can be redefined. At this point, you can use the can_redef_table method of the dbms_redefinition package to determine.

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

PL/SQL procedure successfully completed

Executed in 0.016 seconds

Notice the third parameter of the method, using the primary key or the rowid method. In essence, Online Redefinition uses materialized view Materialized View technology. Process definition records are primary key and rowid policies. In general, we recommend that the data table have an explicit primary key, that is, using cons_use_pk. If you want to use rowid, use dbms_redefinition.cons_use_rowid.

After passing the check, you can move on to the next step, defining the target data table format. Whatever the change, we need to create an intermediate table interim that implements our "expected" data table definition. These include table types, column definitions, partition definitions, indexes, and so on. Note, however, that the constraint (primary foreign key) may not be defined in it.

SQL > create table t_interim as select * from dba_objects where 1: 0

Table created

Executed in 0.032 seconds

Obviously, we want to add some columns to the data table T.

At this point, we can begin the redefinition process. Use the start_redef_table method of dbms_redefinition.

SQL > exec dbms_redefinition.start_redef_table ('SCOTT','T','T_INTERIM',col_mapping = >' object_id object_id, object_name object_name, owner owner',options_flag = > dbms_redefinition.cons_use_pk)

PL/SQL procedure successfully completed

Executed in 0.625 seconds

Note the parameter col_mapping here, where the column relationship between the source data table and the intermediate table is defined in terms of paired key values. Each column relationship is written according to the format of.

For the processing of constraints, if there are constraint objects that need to be converted, we can use the copy_table_dependents method to copy the constraints of the source data table to the target object.

SQL > declare

2 error_count number

3 begin

4 error_count: = 0

5 dbms_redefinition.copy_table_dependents (uname = > 'SCOTT',orig_table = >' T')

6 int_table = > 'titled intertim'

7 copy_indexes = > dbms_redefinition.cons_orig_params

8 num_errors = > error_count)

9 dbms_output.put_line (to_char (error_count))

10 end

11 /

0

PL/SQL procedure successfully completed

Executed in 4.265 seconds

This method has many parameters, which are defined as follows:

-- NAME: copy_table_dependents

--

-INPUTS: uname-schema name

-- orig_table-name of table to be re-organized

-- int_table-name of interim table

-- copy_indexes-integer value indicating whether to

-- copy indexes

-0-don't copy

-1-copy using storage params/tablespace

-- of original index

-copy_triggers-TRUE implies copy triggers, FALSE otherwise

-copy_constraints-TRUE implies copy constraints, FALSE

-- otherwise

-copy_privileges-TRUE implies copy privileges, FALSE

-- otherwise

-ignore errors-TRUE implies continue after errors, FALSE

-- otherwise

-- num_errors-number of errors that occurred while

-- cloning ddl

-copy_statistics-TRUE implies copy table statistics, FALSE

-- otherwise.

-- If copy_indexes is 1, copy index

-- related statistics, 0 otherwise.

-copy_mvlog-TRUE implies copy table's MV log, FALSE

-- otherwise.

PROCEDURE copy_table_dependents (uname IN VARCHAR2

Orig_table IN VARCHAR2

Int_table IN VARCHAR2

Copy_indexes IN PLS_INTEGER: = 1

Copy_triggers IN BOOLEAN: = TRUE

Copy_constraints IN BOOLEAN: = TRUE

Copy_privileges IN BOOLEAN: = TRUE

Ignore_errors IN BOOLEAN: = FALSE

Num_errors OUT PLS_INTEGER

Copy_statistics IN BOOLEAN: = FALSE

Copy_mvlog IN BOOLEAN: = FALSE)

Using various copy_xxx parameters, we can finely define which constraint dependencies will be copied to the target table.

When it's over, we need to end the definition process using the finish_redef_table method.

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

PL/SQL procedure successfully completed

Executed in 1.406 seconds

Finally, we look at the results.

SQL > desc t

Name Type Nullable Default Comments

--

OWNER VARCHAR2 (30) Y

OBJECT_NAME VARCHAR2 (128Y)

SUBOBJECT_NAME VARCHAR2 (30) Y

OBJECT_ID NUMBER Y

DATA_OBJECT_ID NUMBER Y

OBJECT_TYPE VARCHAR2 (19) Y

CREATED DATE Y

LAST_DDL_TIME DATE Y

TIMESTAMP VARCHAR2 (19) Y

STATUS VARCHAR2 (7) Y

TEMPORARY VARCHAR2 (1) Y

GENERATED VARCHAR2 (1) Y

SECONDARY VARCHAR2 (1) Y

NAMESPACE NUMBER Y

EDITION_NAME VARCHAR2 (30) Y

SQL > desc t_interim

Name Type Nullable Default Comments

--

OBJECT_ID NUMBER

OBJECT_NAME VARCHAR2 (128Y)

OWNER VARCHAR2 (30) Y

SQL > select count (*) from t

COUNT (*)

-

75192

The fields of the data table T are added, and the structure of the intermediate table is set to the original. The constraint content, that is, the original primary key, is also retained.

-- in the dbms_metadata.getddl output fragment

"EDITION_NAME" VARCHAR2 (30)

CONSTRAINT "PK_T_ID" PRIMARY KEY ("OBJECT_ID")

At this point, we have completed the simplest redefinition process. It is roughly divided into five steps:

Determine whether the data table can support redefinition and define the Interim structure of the intermediate table

U start the redefinition process using dbms_redefinition 's start_redef_table method

Copy constraints, redefine register constraint information content

Synchronous DML operations in the online process (Optional, demonstrated in a later example)

ü end procedure finish_redef_table operation

We will continue to discuss the details of online redefinition, such as implementation, synchronization of intermediate DML operations, locking mechanism, and functional principles of various methods.

This is the answer to the question about what is the online redefinition of Oracle Online Redefinition. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.