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; 


10 Responses so far.

  1. Maybe try with:

    cast(my_timestamp as date)

    to avoid all the to_date/to_char stuff

  2. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.

    Branding Services in Chennai

  3. priya says:

    It is really a great and useful piece of info. I’m glad that you shared this helpful info with us. Please keep us informed like this. Thank you for sharing.

    Online Training in Chennai

  4. Shalini says:

    This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..
    seo company in india
    Digital Marketing Company in india

  5. Aasha says:

    Thanks for this blog. provided great information. All the details are explained clearly with the great explanation. Thanks for this wonderful blog. Step by step processes execution are given clearly.Know the details about different thing.
    Web Design Company in Chennai

  6. we have almost all country students as our subscribers for online course.We have 10+ years of experience we can serve various ascent people. oracle fusion Cloud HCM online training at erptree.com is worlds best online training center. we have excelent knowledge sharing Platform we have user friendly website where you will be provided with all the required details and Self-paced DEMO videos. we have our branches in pune, gurgaon, noida, india, usa, uk, uae, oracle fusion hcm training, fusion Procurement training, fusion hcm, scm training


  7. your site is genuine to view sir
    thank you for your valluable information sir
    regards
    http://www.erptree.com/course/oracle-fusion-procurement-online-training-in-kolkata/

  8. I am truly inspired with this blog! Clear clarification of issues is given and it is interested in everybody. A debt of gratitude is in order for sharing this post. I am amazed to see how well you organized this post. your blog style is also very impressive and beautiful. I am very impressed. Great work!
    oracle fusion hcm training at Mindmajix

  9. your post conveys a good messages and interesting things and give more updates.
    digital marketing company in india

Powered by Blogger.

Page Views

- Copyright © Emre Baransel - Oracle Blog -Metrominimalist- Powered by Blogger - Designed by Johanes Djogan -