Oracle 12.2 uses manual creation and registration of dependent objects to perform online redefinition

The following example uses the method of manually creating and registering dependent objects to perform an online redefinition operation. The original table is created as follows:

SQL > create table jy.t1 (C1 number); Table createdSQL > create index jy.t1_idx_1 on jy.t1 (C1); Index created

Suppose column C1 becomes c2 after an online redefinition. In this case, the copy_table_dependents procedure is used to attempt to create an index t1_idx on the C1 column of the intermediate table, because an error occurs if there is no column C1. Therefore, an index must be created on column c2 and then registered.

The online redefinition operation is as follows:

1. Log in to the database with the user to perform the online redefinition operation

SQL > conn jy/jy@jypdbConnected.

two。 Verify that the original table T1 can perform online redefinition operations

SQL > begin 2 dbms_redefinition.can_redef_table (3 uname = > 'jy', 4 tname = >' t 1 tname, 5 options_flag = > DBMS_REDEFINITION.CONS_USE_ROWID), 6 end; 7 / PL/SQL procedure successfully completed.

3. Manually create intermediate table jy.int_t1 and create index jy.int_t1_idx_1 on column c2

SQL > create table jy.int_t1 (c2 number); Table created.SQL > create index jy.int_t1_idx_1 on jy.int_t1 (c2); Index created.

4. Start an online redefinition operation

SQL > begin 2 dbms_redefinition.start_redef_table (3 uname = > 'jy', 4 orig_table = >' t 1 col_mapping, 5 int_table = > 'int_t1', 6 col_mapping = >' C1 C 2, 7 options_flag = > DBMS_REDEFINITION.CONS_USE_ROWID), 8 end; 9 / PL/SQL procedure successfully completed.

5. Register raw (index t1_idx_1) and intermediate (int_t1_idx_1) dependent objects

SQL > begin 2 dbms_redefinition.register_dependent_object (3 uname = > 'jy', 4 orig_table = >' t 1', 5 int_table = > 'int_t1', 6 dep_type = > DBMS_REDEFINITION.CONS_INDEX, 7 dep_owner = >' jy', 8 dep_orig_name = >'t 1 updated, 9 dep_int_name = > 'int_t1_idx_1'), 10 end; 11 / PL/SQL procedure successfully completed.

6. Copy dependent objects

SQL > declare 2 num_errors pls_integer; 3 begin 4 dbms_redefinition.copy_table_dependents (5 uname = > 'jy', 6 orig_table = >' t 1 orig_table, 7 int_table = > 'int_t1', 8 copy_indexes = > DBMS_REDEFINITION.CONS_ORIG_PARAMS, 9 copy_triggers = > TRUE, 10 copy_constraints = > TRUE, 11 copy_privileges = > TRUE, 12 ignore_errors = > TRUE, 13 num_errors = > num_errors), 14 end; 15 / PL/SQL procedure successfully completed.

7. Optional operation to synchronize intermediate tables

SQL > begin 2 dbms_redefinition.sync_interim_table (3 uname = > 'jy', 4 orig_table = >' t 1 orig_table, 5 int_table = > 'int_t1'), 6 end; 7 / PL/SQL procedure successfully completed.

8. Complete the online redefinition operation

SQL > begin 2 dbms_redefinition.finish_redef_table (3 uname = > 'jy', 4 orig_table = >' t 1 orig_table, 5 int_table = > 'int_t1'); 6 end; 7 / PL/SQL procedure successfully completed.SQL > select dbms_metadata.get_ddl (object_type = >' TABLE',name = > 'T1century object_type = >' JY') from dual DBMS_METADATA.GET_DDL (OBJECT_TYPE= > 'TABLE',NAME= >' T1') SCHEMA= > 'JY')-CREATE TABLE "JY". "T1" ("C2" NUMBER) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255NOCOMPRESS LOGGING TABLESPACE "TEST" 1 row selected.

You can see that the table jy.t1 has been successfully redefined online

9. Wait for the execution of any statement to query the intermediate table to delete it

SQL > desc jy.t1Name Type Nullable Default Comments-----C2 NUMBER YSQL > drop table jy.t1 purge;Table dropped

At this point, the redefinition operation is complete.