DBMS_REDEFINITION Revisited

It has come to my attention that my last post on dbms_redefinition was really too simplistic to be useful. Therefore, I have set up a new example to show some of the true capabilities of dbms_redefinition. So for this example, we will change column data types instead of just the column sizes. Below are the layouts of our source table (CUSTDATA) and our interim table (CUSTDATA_INT):


SQL> desc custdata
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 CRDATTIM                                  NOT NULL CHAR(26)
 CASE_NUMBER                                        CHAR(10)
 LAST_NAME                                          CHAR(30)
 FIRST_NAME                                         CHAR(30)


SQL> desc custdata_int
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 CRDATTIM                                  NOT NULL TIMESTAMP(6)
 CASE_NUMBER                                        VARCHAR2(30)
 LAST_NAME                                          VARCHAR2(30)
 FIRST_NAME                                         VARCHAR2(30)

Note that the custdata table layout is not particularly good, but it is an example of an actual case, this is a table of legacy data that we are unable to get rid of.  I am working on getting our application team to redesign it to a more sensible layout, but that will take time. As can be seen, there is an extensive amount of data changes to go through.  As in our previous example, first we have to verify that the table can be redefined use DBMS_REDEFINITION:

<pre>
SQL> SET SERVEROUTPUT ON
SQL> begin
  2  DBMS_REDEFINITION.CAN_REDEF_TABLE(
  3  uname=>'SCOTT',
  4  tname=>'CUSTDATA',
  5  options_flag=>DBMS_REDEFINITION.cons_use_rowid);
  6  end;
  7  /

PL/SQL procedure successfully completed.

Now that we have verified that the source table can be redefined using dbms_redefinition, we begin the process:


SQL> SET SERVEROUTPUT ON
SQL> begin
  2  DBMS_REDEFINITION.START_REDEF_TABLE(
  3  uname=>'SCOTT',
  4  orig_table=>'CUSTDATA',
  5  int_table=>'CUSTDATA_INT',
  6  col_mapping=>'to_timestamp(crdattim,'||''''||'yyyy-mm-dd-hh24.mi.ss.ff'||''
''||') crdattim,
  7  rtrim(ltrim(case_number)) case_number,
  8  rtrim(ltrim(first_name)) first_name,
  9  rtrim(ltrim(last_name)) last_name',
 10  options_flag=>DBMS_REDEFINITION.cons_use_rowid);
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> begin
  2  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
  3  uname=>'SCOTT',
  4  orig_table=>'CUSTDATA',
  5  int_table=>'CUSTDATA_INT');
  6  end;
  7  /

PL/SQL procedure successfully completed.

So, everything is going well.  Let’s move on to the next step:

 
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  l_num_errors PLS_INTEGER;
  3  begin
  4  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
  5  uname=>'SCOTT',
  6  orig_table=>'CUSTDATA',
  7  int_table=>'CUSTDATA_INT',
  8  copy_indexes => DBMS_REDEFINITION.cons_orig_params,
  9  copy_triggers => TRUE,
 10  copy_constraints => TRUE,
 11  copy_privileges => TRUE,
 12  ignore_errors => FALSE,
 13  num_errors => l_num_errors);
 14  end;
 15  /
DECLARE
*
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1015
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1907
ORA-06512: at line 4

At this point, I abort the process and try and track down the problem.  To abort a dbms_Redefinition process, the command is abort_redef_table, like this:


SQL> begin
  2  DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname=>'SCOTT',orig_table=>'CUSTDATA',int_table=>'CUSTDATA_INT');
  3  End;
  4  /

PL/SQL procedure successfully completed.

The error message does look like a possible Oracle bug.  Let’s see what the Metalink (My Oracle Support) says: I searched on ORA-01442 DBMS_REDEFINITION, and the first result is Document ID 1116785.1, ORA-1442 Error During Online Redefinition – DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS.  Oracle doesn’t actually say it is a bug, and it may not be.  It probably stems from the fact that I created my interim table using a create table as select, then made changes to it.  It looks like I just need to drop the not null constraint on CUSTDATA_INT and start over, so let’s try that. I ran this query:

SQL> select * from dba_constraints where table_name ='CUSTDATA' and owner='SCOTT';
OWNER
-------------------------------------
CONSTRAINT_NAME                C
------------------------------ -
SCOTT
SYS_C0038648                   C

So there is only one constraint, a check constraint.  I will drop that, and see if that fixes the problem.

SQL> alter table custdata_int drop constraint SYS_C0038648;
Table altered.

Sure enough, that fixed it (NOTE: We have to restart at the beginning since we used the ABORT_REDEF_TABLE procedure.)

SQL> SET SERVEROUTPUT ON
SQL> begin
  2  DBMS_REDEFINITION.START_REDEF_TABLE(
  3  uname=>'SCOTT',
  4  orig_table=>'CUSTDATA',
  5  int_table=>'CUSTDATA_INT',
  6  col_mapping=>'to_timestamp(crdattim,'||''''||'yyyy-mm-dd-hh24.mi.ss.ff'||''
''||') crdattim,
  7  rtrim(ltrim(case_number)) case_number,
  8  rtrim(ltrim(first_name)) first_name,
  9  rtrim(ltrim(last_name)) last_name',
 10  options_flag=>DBMS_REDEFINITION.cons_use_rowid);
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> SET SERVEROUTPUT ON
SQL> begin
  2  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
  3  uname=>'SCOTT',
  4  orig_table=>'CUSTDATA',
  5  int_table=>'CUSTDATA_INT');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  l_num_errors PLS_INTEGER;
  3  begin
  4  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
  5  uname=>'SCOTT',
  6  orig_table=>'CUSTDATA',
  7  int_table=>'CUSTDATA_INT',
  8  copy_indexes => DBMS_REDEFINITION.cons_orig_params,
  9  copy_triggers => TRUE,
 10  copy_constraints => TRUE,
 11  copy_privileges => TRUE,
 12  ignore_errors => FALSE,
 13  num_errors => l_num_errors);
 14  end;
 15  /

PL/SQL procedure successfully completed.

So, lets finish the process:

 
SQL> SET SERVEROUTPUT ON
SQL> begin
  2  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
  3  uname=>'SCOTT',
  4  orig_table=>'CUSTDATA',
  5  int_table=>'CUSTDATA_INT');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> alter table custdata drop unused columns;
Table altered.

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'CUSTDATA', ca
scade=>true);

PL/SQL procedure successfully completed.

SQL>

SQL> desc custdata;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 CRDATTIM                                           TIMESTAMP(6)
 CASE_NUMBER                                        VARCHAR2(30)
 LAST_NAME                                          VARCHAR2(30)
 FIRST_NAME                                         VARCHAR2(30)

Note a couple of steps I left off in my earlier example: drop unused columns and gathering stats.  The unused column was used in the redefinition process because I was using the ROWID option (cons_use_rowid).  Oracle added at the start of the process to keep track of rows, and Oracle automatically marks it as unused once the process is complete, but it is a best practice to drop the column when the process is complete.

2 Responses to “DBMS_REDEFINITION Revisited”

  1. Carlos Henrique Yakithi Furushima Says:

    Andrew Kerber,

    His solution posted this article for the error “ORA-01442: column to be modified to NOT NULL is already NOT NULL”, is time consuming/arduous/time consuming. The note written by Oracle (Metalink) also is no unusual (does not offer an intelligent solution to the problem)

    If the table has many columns is unfeasible / impossible to create the structure manually (there is a possibility of human error. If this happens fucked !! lol).

    A simple, easy and intelligent solution is to run a “CREATE TABLE AS SELECT” (CTAS), excluding the “NOT NULL” constraint, as is done in the example below:

    create table CUSTDATA_INT
    AT
    ((Select * from CUSTDATA where ‘Black’ = ‘White’)
    minus
       (Select * from CUSTDATA where ‘Black’ = ‘White’)
    );

    Carlos Furushima
    Oracle ACE – Brazil

    • dbakerber Says:

      Your solution may work, but I dont see it as any easier than the solution I posted. There are many ways to extract DDL for a table, dbms_ddl for example, and editing the results of that are easy enough. I have used the CTAS method also for tables with a small number of columns.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: