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; 


76 Responses so far.

  1. Maybe try with:

    cast(my_timestamp as date)

    to avoid all the to_date/to_char stuff

  2. Unknown says:

    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. Unknown says:


    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. Unknown says:

    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. Unknown says:

    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

  10. Unknown says:

    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

  11. srinu says:

    Really very helpful article , Thank you for sharing

    Oracle Fusion SCM Online Training

  12. sushma says:

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

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

    Oracle Taleo Online Training

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


    oracle training in bangalore

  15. Unknown says:

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

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

  17. 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

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

    oracle fusion scm online training

  19. Unknown says:

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

  20. Unknown says:

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

  21. 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.

  22. Very interesting blog and really very helpful article

    Oracle Fusion HCM Online Training

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

    Oracle Fusion SCM Online Training

  24. Unknown says:

    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

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

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

  27. shivani says:





    Thanks for sharing a useful information.. we have learnt so much information from your blog..... keep sharing
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

  28. Thank you for sharing with us, and we sincerely hope you will continue to update or post other articlesthanks for sharing such a nice information i really appreciate your work in this and looking for more such good posts in the future thank you

  29. nice post,Thanks for sharing...
    keyword

  30. Such a nice blog, I really like what you write in this blog, I also have some relevant information about if you want more information.

    Workday Online Training

  31. Thank you for sharing such a nice and really very helpful article

    Oracle Fusion HCM Online Training


  32. Very interesting blog Thank you for sharing such a nice and interesting blog and really very helpful article
    Digital Marketing Training In Hyderabad
    sem training in hyderabad
    seo training in hyderabad
    SMM Training In Hyderabad


  33. Very interesting blog Thank you for sharing such a nice and interesting blog and really very helpful article
    Digital Marketing Training In Hyderabad
    sem training in hyderabad
    seo training in hyderabad
    SMM Training In Hyderabad

  34. Anonymous says:


    smart outsourcing solutions is the best outsourcing training
    in Dhaka, if you start outsourcing please
    visit us: graphic design training
    digital marketing training

  35. This is the exact information I am been searching for, Thanks for sharing the required infos with the clear update and required points. To appreciate this I like to share some useful information.mobile application testing training in bangalore

  36. Deepa says:

    It is very good and useful for students and developer. Learned a lot of new things from your post. n Salesforce Training Sydney

  37. Pushba says:

    Thank you for sharing with us, and we sincerely hope you will continue to update or post other articlesthanks for sharing such a nice information i really appreciate your work in this and looking for more such good posts in the future thank you
    IELTS Coaching in chennai

    German Classes in Chennai

    GRE Coaching Classes in Chennai

    TOEFL Coaching in Chennai

    spoken english classes in chennai | Communication training

  38. 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'sDevOps Training in Bangalore

    DevOps Training

    DevOps Online Training


    DevOps Training in Hyderabad

    DevOps Online Training in Chennai

    DevOps Training in Coimbatore

  39. jdgvks says:

    An overwhelming web journal I visit this blog, it's unfathomably amazing. Unusually, in this present blog's substance made inspiration driving truth and reasonable. The substance of data is enlightening
    hadoop training in bangalore

    oracle training in bangalore

    hadoop training in acte.in/oracle-certification-training">oracle training

    oracle online training

    oracle training in hyderabad

    hadoop training in chennai

  40. sri says:

    Excellent content ,Thanks for sharing this .,
    Leanpitch provides online training in CSPO during this lockdown period everyone can use it wisely.
    Join Leanpitch 2 Days CSPO Certification Workshop in different cities.
    CSPO certification online

    Certified scrum product owner

  41. sri says:

    Excellent content ,Thanks for sharing this .,
    Leanpitch provides online training in CSPO during this lockdown period everyone can use it wisely.
    Join Leanpitch 2 Days CSPO Certification Workshop in different cities.
    CSPO online certification

    CSPO certification cost


  42. Go to turbotax.ca/download Set up a TurboTax account if you don't already have one. If you do, sign in. turbotax.ca/download Enter your license code in the pop up window.Select your operating system, and select Get Download.Download your software and install. turbotax.ca/download is a software package that helps you manage your income tax returns. Intuit produces it. turbotax.ca/download It is one of the top software that allows you to control your income tax returns. turbotax.ca/download is really a tax free preparation software that coaches you to process and file your earnings in the appropriate method. turbotax.ca/download This program is readily available for both federal and state tax returns. turbotax.ca/download Yet before by using this software in your apparatus, the TurboTax login procedure is crucial. Go to turbotax.ca/download to sign in or create an account, turbotax.ca/download and then enter your activation code (even if you've already started your taxes for tax year 2020, you’ll still need to activate the code).

  43. Get more from Microsoft 365 with Sherweb. microsoft365.com/setup Sherweb is a value-added cloud solutions provider dedicated to your success.  microsoft365.com/setup Go through webpage, Sign in, and enter the product key to download and install Microsoft 365 or Office on your computer. microsoft365.com/setup After you download Office 365 from and install it. microsoft365.com/setup Microsoft 365 or Office 365 is multiple products collections in one software that is available at and every kind of microsoft365.com/setup individual can use these products for various uses like students, home, professionals, enterprise, and more.

  44. Roblox Gift Cards are the easiest way to load up on credit for Robux or a .www.roblox.com/redeem Included with each gift card is a free virtual item that's granted upon redemption.\ How do I get a Roblox promotional code? www.roblox.com/redeem You may receive a Roblox promo code from one of our many events or giveaways. www.roblox.com/redeem Go to the Roblox gift card redemption page in a web browser. Log into your account. www.roblox.com/redeem Enter your gift card PIN and click "Redeem.www.roblox.com/redeem is a massive gaming platform, full of tons of games created by both users and companies. Most of these games are free, all of them are child friendly, www.roblox.com/redeem and users can either create their own games or just play one of the many other games that are available. www.roblox.com/redeem Roblox is a huge platform, often compared to Minecraft, however, there is no upfront cost when it comes to playing on Roblox.Roblox is an online game platform and game creation system developed by Roblox Corporation. It allows users to program games and play games created by other users. For more updates click www.roblox.com/redeem here. Following are the sources from where you can get paid and free Roblox Gift Card. www.roblox.com/redeem We are also running Roblox free Gift Card giveaway contest where you can participate and win free Roblox Gift Card weekly. Buy www.roblox.com/redeem Gift Cards at your local retailers as well as their online stores. In the United States and Canada, you can also purchase them online here.

  45. especia associate says:

    Thanks For sharing. Especia Associates provide outsourcing Accounting Services. Finance and Accounting outsourcing service for measuring the Return on Investment(ROI) on every step you have taken, to make sure of your increased profits. A decision in haste might be working well short term but often have long term staggering consequences. Accounting and Bookkeeping services team to provide you the daily input of company records from a time-to-time basis. Enriching you with the ongoing market and whereabouts of the places to strike or back down as necessary. if you need Accounting Services call at 9310165114 or visit us Outsourcing Accounting

  46. Anonymous says:

    Looks like a good time was had by all. Funny post.
    Pakistani Drama Website

  47. Anonymous says:

    Thank you once again for your love and willingness to share your feelings
    SEO Firm Chicago

    Digital Evrima

  48. Thanks for this wonderful blog. All the details are explained clearly with the great explanation. It is much interesting so please keep updated like this. - SEO Company In Indore

  49. Hisabkitab is a professional accounting firm that offers bookkeeping, GST consulting, tax return preparation, and other related services to businesses and individuals. Their experienced accountants use the latest software and tools to provide accurate financial statements and reports, while their tax experts offer expert advice and assistance to optimize tax liabilities. Hisabkitab provides customized financial solutions to meet the specific needs and goals of clients, allowing them to focus on growing their business.

  50. Sharma says:

    Hisabkitab offers reliable bookkeeping services in Surat for businesses of all sizes. Our team of experienced accountants ensures accurate and timely financial reporting, tax compliance, and bookkeeping services to help you make informed business decisions. Our services include bank reconciliation, payroll management, accounts payable and receivable, financial statements, and more. With our advanced technology and software, we guarantee confidentiality and security of your financial information. Let Hisabkitab be your partner in financial success. Contact us today for a free consultation.

  51. A helpful guide simplifying timestamp to date conversion. This blog provides a clear walkthrough using SQL, making date manipulation easier for developers. A valuable resource for anyone working with databases.
    accounting and bookkeeping

  52. Whether you're celebrating a milestone or seeking an exquisite evening out, Silver Spoon at Britannia Restaurant Calgary, promises an experience that lingers in your memory. Immerse yourself in a world where culinary artistry meets refined ambiance, and allow Silver Spoon to redefine your expectations of gastronomic delight.

  53. Trucks are the backbone of transportation, and our mechanics understand the significance of proper truck maintenance. Whether it's routine oil changes, engine tune-ups, or addressing complex mechanical issues, we have the expertise to keep your trucks running efficiently and reliably.

  54. To provide top-notch service, Lucky Mechanic Truck invests in the latest diagnostic tools and equipment. This ensures that they can accurately diagnose issues and perform repairs efficiently, saving you time and money.

  55. Lucky Mechanic Truck is committed to reducing its environmental footprint. They implement eco-friendly practices and disposal methods, ensuring that their operations are as sustainable as possible.

  56. For an authentic taste of India, visit King22 for desi food near me. Our Indian cuisine is a burst of flavors that transports you straight to the subcontinent.

  57. Whether you have a specific color in mind or want to match a particular design theme, Century Bathtub offers customization options to bring your vision to life.
    The materials used by Century Bathtub are designed to withstand daily use and maintain their beauty over time. You can expect long-lasting results from their refinishing services.
    By choosing refinishing over replacement, you contribute to sustainability by reducing waste and minimizing the impact on landfills.

  58. At Surajvanshi Dawakhana, we stand firmly behind the belief that true healing encompasses not only physical health but also mental and emotional well-being. Our holistic approach goes beyond addressing the symptoms of ED; it aims to restore balance and vitality to your entire being. We consider every aspect of your health, emphasizing the interconnectedness of the mind, body, and spirit.

  59. In Karachi and beyond, Orison Movers is the name synonymous with secure and expeditious fashion movement. We invite you to experience the difference of working with a team dedicated to ensuring your possessions arrive safely, on schedule, and with the care they merit.

    Put your trust in Orison Movers, your partner in precision and peace of mind for every move, and let us help you embark on your next chapter with absolute confidence.

Powered by Blogger.

Page Views

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