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 Copy_table_dependents.
First look at the example under 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
Sql> SELECT table_name, constraint_name
2 from User_constraints
3 WHERE table_name in (' T ', ' t_inter ');
This article URL address: http://www.bianceng.cn/database/Oracle/201410/45466.htm