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; 


42 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

  10. Great site that has principles and different estimations concerning the different people’s ideas and also validates them, this site as well has a famous way of keeping in the correct comments regarding the a variety of topics like ORACLE FUSION Training

  11. Very nice post here and thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.For more information visit our website.

    Oracle Fusion Financials Online Training

  12. Simhadri says:

    Thank you for sharing such a nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information.

    Oracle Fusion HCM Online Training

  13. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    oracle fusion financials training in hyderabad

  14. srinu says:

    Really very helpful article , Thank you for sharing

    Oracle Fusion SCM Online Training

  15. Sravani says:

    Awesome post……. your article is really informative and helpful for me and other bloggers too

    oracle fusion hcm online training

  16. sushma says:

    Hey Really Thanks for sharing the best information regarding category,hope you will write more great blogs.
    Oracle Fusion Financial Online Training

  17. raju says:

    Thank you for sharing such a nice and interesting blog.
    Oracle Fusion Financials Online Training

  18. Really excellent information and thank you for giving your valuable information

    Oracle Fusion Financials online training

  19. Thank you for sharing such a nice and interesting blog.

    Oracle Taleo Online Training

  20. believe there are many more pleasurable opportunities ahead for individuals that looked at your site.


    oracle training in bangalore

  21. Oracle says:

    Really excellent information and thank you for giving your valuable information

    Oracle Fusion SCM Online Training

  22. Naga says:

    Very interesting blog, keep posting such an informative post.

    Oracle Fusion Financials Online Training

  23. leela says:

    Really very helpful article, Thank you for sharing interesting blogs.

    Oracle Fusion Technical Online Training

  24. Thanks for your information sharing, the nice blog.Oracle Fusion SCM training


  25. Your website is very good and nice information was provided on your site, thanks for sharing.
    Oracle Fusion Financials Training in Ameerpet

  26. kiran mai says:

    You really did a great job. I found your blog very interesting and very informative. I think your blog is great information source & I like your way of writing and explaining the topics. Know More Details About Oracle or Oracle Application Framework click here.

  27. Thank you for sharing such a nice and interesting blog and very useful details with me. Thanks for your great effort.

    oracle fusion financials online training

  28. Really very helpful article, Thank you for sharing such a nice and interesting blog

    oracle fusion scm online training

  29. It's A Great Pleasure reading your Article Bala Guntipalli Thanks for posting.

  30. Your website is very good and nice information was provided on your site, thanks for sharing.
    Oracle Fusion Financials Training

  31. Unknown 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.

  32. Very interesting blog and really very helpful article

    Oracle Fusion HCM Online Training

  33. Thank you for sharing such a nice and very interesting blog

    Oracle Fusion SCM Online Training

  34. Awesome post. your article is really informative and helpful for me and other bloggers too

    Oracle Fusion Technical Online Training

  35. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.
    Oracle Fusion Financials Online Training

  36. Thank you for providing useful information and this is the best article blog for the students. learn Oracle Fusion Financials Online Training.

    Oracle Fusion Financials Online Training

  37. Thank you for sharing such a valuable article with good information containing in this blog. learn Oracle Fusion Technical Online Training.

    Oracle Fusion Technical Online Training

  38. Very interesting blog Thank you for sharing such a nice and interesting blog and really very helpful article Oracle Fusion Financials Online Training

  39. Thank you for sharing such a nice and interesting blog and really very helpful article
    Oracle Fusion Financials Online Training

  40. Very interesting blog Really excellent information and thank you for giving your valuable information
    Oracle Fusion Financials Online Training

  41. Thank you for sharing such a nice and interesting blog and really very helpful article
    Oracle Fusion SCM Online Training

Powered by Blogger.

Page Views

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