TIMESTAMP to DATE Conversion with Online Redefinition

Online redefinition is a great way to make structural change on "big" tables having "lots of" DML. Using online redefinition, partitioning-nonpartitioning, adding-dropping columns, changing column data types, moving to another tablespace and more can be done with a very small unavailability of the table when compared with direct operations.

Here are some online redefinition MOS notes which make life easier:

Dbms_Redefinition Online Reorganization Of Tables (Doc Id 149564.1)
How To Re-Organize A Table Online (Doc Id 177407.1)
How To Shrink A Table Using Online Redefinition(Doc Id 1357878.1)
How To Compress A Table While It Is Online(Doc Id 1353967.1)
How To Move A Table To A New / Different Tablespace While It Is Online
How To Convert Long Column To Clob Using Dbms_Redefinition Package (Doc Id 251417.1)
Online Redefinition Of Table Using Rowid Option (Doc Id 210407.1)
An Example Of A Complex Online Table Redefinition (Dbms_Redefinition) (Doc Id 1358236.1)

My case was to convert a TIMESTAMP column to DATE.

In order to prepare a test environment:

SQL> create table table1 (col1 number, col2 timestamp);

SQL> insert into table1 values (1,systimestamp);

SQL> commit;

SQL> create table table2 (col1 number, col2 date);

SQL> BEGIN

DBMS_REDEFINITION.CAN_REDEF_TABLE

(uname => 'eb'

,tname => 'table1'

,options_flag => dbms_redefinition.cons_use_rowid);

END;

PL/SQL procedure successfully completed.

When converting column data types, "col_mapping" parameter must be defined onDBMS_REDEFINITION.START_REDEF_TABLE procedure. If not following error raises:

SQL> BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE

(uname => 'eb'

,orig_table => 'table1'

,int_table => 'table2'

,options_flag => dbms_redefinition.cons_use_rowid);

END;

ERROR at line 1:

ORA-42016: shape of interim table does not match specified column mapping

When we specify TO_DATE function on the col_mapping parameter in our case :

SQL> BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE

(uname => 'eb'

,orig_table => 'table1'

,int_table => 'table2'

,col_mapping => 'col1 col1,to_date(col2) col2'

,options_flag => dbms_redefinition.cons_use_rowid);

END;

ERROR at line 1:

ORA-12008: error in materialized view refresh path

ORA-01830: date format picture ends before converting entire input string

As a workaround to this problem, i used first TO_CHAR and then TO_DATE functions and it worked.

SQL> BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE

(uname => 'eb'

,orig_table => 'table1'

,int_table => 'table2'

,col_mapping => 'col1 col1,TO_DATE(TO_CHAR(col2,''dd/MM/yyyy hh24/mi/ss''),''dd/MM/yyyy hh24/mi/ss'') col2'

,options_flag => dbms_redefinition.cons_use_rowid);

END;

PL/SQL procedure successfully completed.

SQL> BEGIN

DBMS_REDEFINITION.FINISH_REDEF_TABLE

(uname => 'eb'

,orig_table => 'table1'

,int_table => 'table2');

END;

PL/SQL procedure successfully completed.

SQL> desc table1

Name Null? Type

------------- -------- ---------------

COL1 NUMBER

COL2 DATE

Note:

On the other hand when converting from DATE to TIMESTAMP you can use only TO_TIMESTAMP function with no errors on 11gR2. For 10g & 11gR1 there's an issue and you get "ORA-42016: shape of interim table does not match specified column mapping" error. Following note offers a workaround for this issue by using a user defined function.

How to Convert Date Column to Timestamp Using DBMS_REDEFINITION Package? (Doc ID 556283.1)

create or replace function convert_date_to_ts(mydate date) return timestamp

is

begin

return to_timestamp(mydate);

end;

/

BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE('scott','sno_date2','sno_timestamp2','myid myid, convert_date_to_ts(mydate) mydate', dbms_redefinition.cons_use_pk);

end;

/