Oracle 10g online redefinition new feature: Automatic renaming of associated objects (ii)

9i Online redefinition There is a problem, after the completion of online redefinition, the table's name, although the same, but the index, constraints, triggers and other associated objects will change the name, sometimes this will bring some problems, but to manually modify after the event, will be more trouble.

10g online redefinition solves this problem. If the objects are created using Copy_table_dependents, the associated objects are automatically changed to their original names when the redefinition operation completes. If it is a manually created association object, you can take advantage of the register_dependent_object procedure, and all associated objects that perform the Register_dependent_object procedure are automatically renamed after the redefinition operation completes.

This article looks at the example of Register_dependent_object.

The previous article describes the example of copy_table_dependents in 10g, which is an example of creating an associated object manually and then using Register_dependent_object.

First, let's look at the example of 9i:

Sql> SELECT * from V$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition release9.2.0.4.0-production

Pl/sql Release 9.2.0.4.0-production

CORE 9.2.0.3.0 Production

TNS for 32-bit windows:version 9.2.0.4.0-production

Nlsrtl Version 9.2.0.4.0-production

Sql> CREATE TABLE T as SELECT rownum ID, a.* from User_objects A;

Table has been created.

sql> ALTER TABLE T ADD CONSTRAINT pk_t PRIMARY KEY (ID);

The table has changed.

sql> ALTER TABLE T ADD CHECK (ID > 0);

The table has changed.

Sql> CREATE INDEX ind_t_name on T (object_name);

The index has been created.

sql> CREATE OR REPLACE TRIGGER tri_t

2 before INSERT on T

3 for each ROW

4 BEGIN

5 NULL;

6 end;

7/

Trigger has been created

sql> CREATE TABLE T_inter

2 PARTITION by HASH (ID)

3 Partitions 4

4 as SELECT rownum ID, a.*

5 from User_objects A

6 WHERE 1 = 2;

Table has been created.

sql> ALTER TABLE t_inter ADD CONSTRAINT pk_t_inter PRIMARY KEY (ID);

The table has changed.

sql> ALTER TABLE t_inter ADD CHECK (ID > 0) DISABLE;

The table has changed.

Sql> CREATE INDEX ind_t_inter_name on T_inter (object_name);

The index has been created.

sql> CREATE OR REPLACE TRIGGER tri_t_inter

2 before INSERT on T_inter

3 for each ROW

4 BEGIN

5 NULL;

6 end;

7/

Trigger has been created

Sql> SET serverout on SIZE 1000000

Sql> EXEC dbms_redefinition. Can_redef_table (USER, ' T ')

The PL/SQL process has completed successfully.

Sql> EXEC dbms_redefinition. Start_redef_table (USER, ' T ', ' t_inter ')

The PL/SQL process has completed successfully.

Sql> SELECT table_name, index_name

2 from User_indexes

3 WHERE table_name in (' T ', ' t_inter ');

TABLE_NAME Index_name

------------------------------ ------------------------------

T_inter Ind_t_inter_name

T Ind_t_name

T pk_t

T_inter Pk_t_inter

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45467.htm