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

In the article "[REDEFINITION] cannot use dbms_redefinition to complete column type adjustment (ORA-42016)", it was mentioned that when modifying the primary key column type, the ORA-42016 error could not be completed due to the inconsistent column type, and the online redefinition could not be completed.

This problem can be accomplished with the help of dbms_redefinition.cons_use_rowid combined with character function (to_char).

The solution is as follows for reference.

1. Create table T1 with a primary key column of type NUMBER

Sec@ora10g > create table T1 (x NUMBER (19) primary key)

Table created.

Sec@ora10g > insert into T1 select rownum from all_objects

11944 rows created.

Sec@ora10g > commit

Commit complete.

Sec@ora10g > desc T1

Name Null? Type

X NOT NULL NUMBER (19)

Sec@ora10g > select count (*) from T1

COUNT (*)

-

11944

two。 Create an intermediate table T1. Note that the type of the primary key column is VARCHAR2, not NUMBER.

Sec@ora10g > create table T2 (x varchar2 (20) primary key)

Table created.

Sec@ora10g > desc T2

Name Null? Type

X NOT NULL VARCHAR2 (20)

Sec@ora10g > select count (*) from T2

COUNT (*)

-

0

3. Ensure the smooth execution of the online redefinition and grant the user the required permissions.

Sec@ora10g > conn / as sysdba

Connected.

Sys@ora10g > grant EXECUTE_CATALOG_ROLE,CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE,LOCK ANY TABLE,SELECT ANY TABLE to sec

Grant succeeded.

4. Use rowid to complete online redefinition

1) verify that it can be redefined online

Sec@ora10g > exec dbms_redefinition.can_redef_table ('SEC','T1',dbms_redefinition.cons_use_rowid)

PL/SQL procedure successfully completed.

Note: this command is equivalent to the following command

Sec@ora10g > exec dbms_redefinition.can_redef_table ('SEC','T1',2)

2) take a look at the structure and data of target table T1 and intermediate table T2 at this time

Sec@ora10g > desc T1

Name Null? Type

X NOT NULL NUMBER (19)

Sec@ora10g > desc T2

Name Null? Type

X NOT NULL VARCHAR2 (20)

Sec@ora10g > select count (*) from T1

COUNT (*)

-

11944

Sec@ora10g > select count (*) from T2

COUNT (*)

-

0

The structure and data remain unchanged.

3) start online redefinition

Sec@ora10g > exec dbms_redefinition.start_redef_table ('SEC','T1','T2','to_char (x) x stories, dbms_redefinition.cons_use_rowid)

PL/SQL procedure successfully completed.

Note: this command is equivalent to the following command

Sec@ora10g > exec dbms_redefinition.start_redef_table ('SEC','T1','T2','to_char (x) x stories, 2)

For a description of the content of the start_redef_table parameter, please refer to the following.

PROCEDURE START_REDEF_TABLE

Argument Name Type In/Out Default?

UNAME VARCHAR2 IN

ORIG_TABLE VARCHAR2 IN

INT_TABLE VARCHAR2 IN

COL_MAPPING VARCHAR2 IN DEFAULT

OPTIONS_FLAG BINARY_INTEGER IN DEFAULT

ORDERBY_COLS VARCHAR2 IN DEFAULT

PART_NAME VARCHAR2 IN DEFAULT

4) take a look at the structure and data of target table T1 and intermediate table T2 at this time

Sec@ora10g > desc T1

Name Null? Type

X NOT NULL NUMBER (19)

Sec@ora10g > desc T2

Name Null? Type

X NOT NULL VARCHAR2 (20)

Sec@ora10g > select count (*) from T1

COUNT (*)

-

11944

Sec@ora10g > select count (*) from T2

COUNT (*)

-

11944

At this time, the structure has not changed, and the data has been synchronized to the intermediate table T2 table.

5) simulate the transaction of target table T1 (take deletion as an example)

Sec@ora10g > delete from T1 where rownum commit

Commit complete.

Sec@ora10g > select count (*) from T1

COUNT (*)

-

1945

Sec@ora10g > select count (*) from T2

COUNT (*)

-

11944

At this point, it is found that there is a change in the data in the T1 table, but no change in the intermediate table T2. It is easy to understand that this can ensure the performance of the system.

At this point, we can use "dbms_redefinition.finish_redef_table" to complete the online redefinition process. You can also use "dbms_redefinition.sync_interim_table" to synchronize data first.

Sec@ora10g > exec dbms_redefinition.sync_interim_table ('SEC',' T1, 'T2')

PL/SQL procedure successfully completed.

Sec@ora10g > select count (*) from T1

COUNT (*)

-

1945

Sec@ora10g > select count (*) from T2

COUNT (*)

-

1945

It can be seen that the contents of data tables T1 and T2 are synchronized again.

6) complete online redefinition

Sec@ora10g > exec dbms_redefinition.finish_redef_table ('SEC','T1','T2')

PL/SQL procedure successfully completed.

7) after completing the online redefinition, let's take a look at the structure and data of target table T1 and intermediate table T2 again.

Sec@ora10g > desc T1

Name Null? Type

X NOT NULL VARCHAR2 (20)

Sec@ora10g > desc T2

Name Null? Type

X NOT NULL NUMBER (19)

Sec@ora10g > select count (*) from T1

COUNT (*)

-

1945

Sec@ora10g > select count (*) from T2

COUNT (*)

-

1945

OK, by this time our goal has been achieved, the primary key type of the target table T1 has been changed from NUMBER type to VARCHAR2 type through online redefinition!

Continue to observe that the X field type of the intermediate table T2 becomes the NUMBER type of the target table after redefinition.

Now that the redefinition mission is complete, the intermediate table T2 can withdraw from the stage of history and delete it.

Sec@ora10g > drop table T2 purge

Table dropped.

5. Summary

The online redefinition function brings great convenience to complete database adjustment on the premise of ensuring the high availability of the system.

The example of using online redefinition to modify the primary key type described in this article is not common, and the online redefinition function is mainly concentrated in the following scenarios:

Online table redefinition enables you to:

* Modify the storage parameters of a table or cluster

* Move a table or cluster to a different tablespace in the same schema

* Add, modify, or drop one or more columns in a table or cluster

* Add or drop partitioning support (non-clustered tables only)

* Change partition structure

* Change physical properties of a single table partition, including moving it to a different tablespace in the same schema

* Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table

* Add support for parallel queries

* Re-create a table or cluster to reduce fragmentation

* Change the organization of a normal table (heap organized) to an index-organized table, or do the reverse.

* Convert a relational table into a table with object columns, or do the reverse.

* Convert an object table into a relational table or a table with object columns, or do the reverse.

Reference link: http://download.oracle.com/docs/. Bles.htm#ADMIN01514

Good luck.

Secooler

10.03.19

-- The End--

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.