Posted by Here’s something to be careful with when it comes to using DBMS_REDEFINITION. Let’s start with the following scenario. I have a table called REDEF1 and the primary key is the OBJECT_ID column.
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.
There are two things we know to be true when it comes to implementing database designs in the real world.
- We pick a primary key is immutable and never changing, and then
- We never get the primary right, and we need to change it 😀
So I am going to add a new surrogate key for my table, but of course I need to backfill the data in that column which either means an extended outage, or I can take care of it using the very nifty DBMS_REDEFINITION package with the column mapping facility.
First I’ll create the new version of my table with the new primary key. I’m going with a GUID just so we know it is different from the previous key.
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.
SQL>
SQL> alter table redef2 add constraint redef2_pk primary key ( new_pk );
Table altered.
Now I am ready to convert my old table to the new version. (Of course, normally we would do a CAN_REDEF_TABLE just to double check that everything is fine, but for the sake of brevity, we will plough straight in!).
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.
Once the initial copy of data has been completed, I could intermittently refresh the copy to bring the data up to date, but assuming we are in a quiet system, I’m now ready to copy over the dependent objects and finish off the redefinition.
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.
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.
And there we go! No errors detected and our new version of REDEF1 is there, replete with its new primary key column.
SQL> desc redef1
Name Null? Type
----------------------------- -------- -----------------
OWNER NOT NULL VARCHAR2(128)
OBJECT_NAME NOT NULL VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE NOT NULL VARCHAR2(23)
NEW_PK NOT NULL RAW(32)
But there is one tiny sleeper problem that most probably no-one will notice until we start running queries on our new table. Look at the parallel setting for the primary key on our freshly defined table.
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 DEFAULT
REDEF2 REDEF1_PK 1
The redefined primary key has a degree of DEFAULT rather than 1. Now given that this is a primary key, and we would hope that the majority of usage for this column is single row lookups, it is unlikely that this will have a detrimental impact on your system, but there is that risk that under certain session or system settings, you might end up with a parallel execution plan when you were not expecting it.
Whilst the above sequence of redefinition steps looks perfectly reasonable, the cause of the issue lies in the sequence in which we carried out our operations. 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
Notice a theme there? The first thing is always the start the redefinition before touching any dependent objects. Let us now repeat the initial redefinition following this rule. (I’ve dropped everything and we’re starting from scratch)
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
So perhaps this is a long way of saying “read the fine manual” but this is an easy mistake to make, and you don’t get any obvious feedback that your primary key has inherited some different settings.
My advice is to always see if COPY_TABLE_DEPENDENTS will take care of everything for you before resorting to manual creation of dependent objects. Only when that cannot be used should you head into manual creation territory.
Learning is not a spectator sport 
Super! Nice pickup and demo, helped me add a PK to a busy table.