oracle plsql DBMS_REDEFINITION - ghdrako/doc_snipets GitHub Wiki
Perusing the DBA Guide in the docs, the steps to carry out a redefinition are one of the following series of operations:
- Start the redefinition, then use COPY_TABLE_DEPENDENTS to control all the dependent objects
- Start the redefinition, then use a combination of COPY_TABLE_DEPENDENTS/REGISTER_DEPENDENT_OBJECT to control all of the dependent objects
- Start the redefinition, then use a combination of COPY_TABLE_DEPENDENTS, manual object creation to control all of the dependent objects
- Start the redefinition, then use manual object creation to control all of the dependent objects
Way to add a PK to a busy table
SQL> create table redef1 as
2 select
3 owner
4 ,object_name
5 ,subobject_name
6 ,object_id
7 ,data_object_id
8 ,object_type
9 from dba_objects
10 where object_id is not null;
Table created.
SQL>
SQL> alter table redef1 add constraint redef1_pk primary key ( object_id );
Table altered.
SQL>
SQL> create table redef2 as
2 select
3 owner
4 ,object_name
5 ,subobject_name
6 ,object_id
7 ,data_object_id
8 ,object_type
9 ,cast(null as raw(32)) new_pk
10 from dba_objects
11 where 1=0;
Table created.
--
-- This time we are NOT creating a primary key on the new table
-- until we have started the redefinition
--
-- alter table redef2 add constraint redef2_pk primary key ( new_pk );
--
SQL>
SQL> begin
2 dbms_redefinition.start_redef_table(
3 uname => user,
4 orig_table => 'redef1',
5 int_table => 'redef2',
6 col_mapping => 'owner owner,
7 object_name object_name,
8 subobject_name subobject_name,
9 object_id object_id,
10 data_object_id data_object_id,
11 object_type object_type,
12 sys_guid() new_pk',
13 options_flag => dbms_redefinition.cons_use_pk);
14 end;
15 /
PL/SQL procedure successfully completed.
SQL> set serverout on
SQL> declare
2 errcount pls_integer;
3 begin
4 dbms_redefinition.copy_table_dependents (
5 uname => user,
6 orig_table => 'redef1',
7 int_table => 'redef2',
8 ignore_errors => true,
9 copy_indexes => 0,
10 copy_triggers => false,
11 copy_constraints => false,
12 copy_privileges => true,
13 copy_statistics => true,
14 num_errors => errcount
15 );
16 dbms_output.put_line('error count = '||errcount);
17 end;
18 /
error count = 0
PL/SQL procedure successfully completed.
--
-- Now I add the primary key
--
SQL> alter table redef2 add constraint redef2_pk primary key ( new_pk );
Table altered.
SQL>
SQL> begin
2 dbms_redefinition.finish_redef_table(
3 uname => user,
4 orig_table => 'redef1',
5 int_table => 'redef2');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> select table_name, index_name, degree
2 from dba_indexes
3 where owner = user
4 and table_name like 'REDEF%'
5 order by 1, 2;
TABLE_NAME INDEX_NAME DEGREE
------------------------------ ------------------------------ ----------------------------------------
REDEF1 REDEF2_PK 1
REDEF2 REDEF1_PK 1