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 timestampisbeginreturn to_timestamp(mydate);end;/BEGINDBMS_REDEFINITION.START_REDEF_TABLE('scott','sno_date2','sno_timestamp2','myid myid, convert_date_to_ts(mydate) mydate', dbms_redefinition.cons_use_pk);end;/
Maybe try with:
cast(my_timestamp as date)
to avoid all the to_date/to_char stuff
Thanks Connor.
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
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
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
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
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
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/
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
your post conveys a good messages and interesting things and give more updates.
digital marketing company in india
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
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
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
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
Really very helpful article , Thank you for sharing
Oracle Fusion SCM Online Training
Awesome post……. your article is really informative and helpful for me and other bloggers too
oracle fusion hcm online training
Hey Really Thanks for sharing the best information regarding category,hope you will write more great blogs.
Oracle Fusion Financial Online Training
Thank you for sharing such a nice and interesting blog.
Oracle Fusion Financials Online Training
Really excellent information and thank you for giving your valuable information
Oracle Fusion Financials online training
Thank you for sharing such a nice and interesting blog.
Oracle Taleo Online Training
believe there are many more pleasurable opportunities ahead for individuals that looked at your site.
oracle training in bangalore
Really excellent information and thank you for giving your valuable information
Oracle Fusion SCM Online Training
Very interesting blog, keep posting such an informative post.
Oracle Fusion Financials Online Training
Really very helpful article, Thank you for sharing interesting blogs.
Oracle Fusion Technical Online Training
Thanks for your information sharing, the nice blog.Oracle Fusion SCM training
Your website is very good and nice information was provided on your site, thanks for sharing.
Oracle Fusion Financials Training in Ameerpet
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.
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
Really very helpful article, Thank you for sharing such a nice and interesting blog
oracle fusion scm online training
It's A Great Pleasure reading your Article Bala Guntipalli Thanks for posting.
Your website is very good and nice information was provided on your site, thanks for sharing.
Oracle Fusion Financials Training
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.
Very interesting blog and really very helpful article
Oracle Fusion HCM Online Training
Thank you for sharing such a nice and very interesting blog
Oracle Fusion SCM Online Training
Awesome post. your article is really informative and helpful for me and other bloggers too
Oracle Fusion Technical Online Training
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
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
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
Very interesting blog Thank you for sharing such a nice and interesting blog and really very helpful article Oracle Fusion Financials Online Training
Thank you for sharing such a nice and interesting blog and really very helpful article
Oracle Fusion Financials Online Training
Very interesting blog Really excellent information and thank you for giving your valuable information
Oracle Fusion Financials Online Training
Thank you for sharing such a nice and interesting blog and really very helpful article
Oracle Fusion SCM Online Training
شركة مكافحة النمل الابيض بالاحساء
Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
Workday HCM Online Training!
Oracle Fusion Financials Online Training
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
Authorized ipad service center in Chennai | Authorized ipod service center in Chennai | ipad service center in chennai | ipod service center in chennai | ipad service center in chennai
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
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
nice post,Thanks for sharing...
keyword
An astounding web diary I visit this blog, it's inconceivably magnificent. Strangely, in this current blog's substance made point of fact and sensible. The substance of information is instructive.
Oracle Fusion Financials Online Training
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Thanks for sharing a useful information.. we have learned so much information from your blog..... keep sharing
Oracle Fusion Financials Online Training
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
A befuddling web diary I visit this blog, it's incredibly grand. Strangely, in this present blog's substance made motivation behind fact and sensible. The substance of information is instructive
Oracle Fusion Financials Online Training
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
A befuddling web diary I visit this blog, it's incredibly grand. Strangely, in this present blog's substance made motivation behind fact and sensible. The substance of information is instructive
Oracle Fusion Financials Online Training
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
A befuddling web diary I visit this blog, it's incredibly grand. Strangely, in this present blog's substance made motivation behind fact and sensible. The substance of information is instructive
Oracle Fusion Financials Online Training
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
An astounding web diary I visit this blog, it's inconceivably magnificent. Strangely, in this current blog's substance made point of fact and sensible. The substance of information is instructive.
A bewildering web journal I visit this blog, it's unfathomably heavenly. Oddly, in this present blog's substance made purpose of actuality and reasonable. The substance of data is informative
A befuddling web diary I visit this blog, it's incredibly grand. Strangely, in this present blog's substance made motivation behind fact and sensible. The substance of information is instructive
Oracle Fusion Financials Online Training
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
BANGALORE TO TIRUPATI PACKAGE
BANGALORE TO TIRUPATI CAR PACKAGES
BANGALORE TO TIRUPATI TOUR PACKAGE
BANGALORE TO TIRUPATI TOUR PACKAGES
BEST TIRUPATI PACKAGE FROM BANGALORE
BANGALORE TO TIRUPATI PACKAGES
ONE DAY TIRUPATI PACKAGE FROM BANGALORE
TIRUPATI BALAJI DARSHAN PACKAGE FROM BANGALORE
BANGALORE TO TIRUPATI TOUR PACKAGES
BANGALORE TO TIRUPATI CAR PACKAGE
Best Car Package from Bangalore to Tirupati
Tirupati Balaji Darshan Package from Bangalore
Tirupati Trip from Bangalore
car package from bengaluru to tirupati
bangalore to tirumala tour
Tirupati Trip from Bangalore
Bangalore to Tirupati Quick Darshan Package
Bangalore to Tirupati Family Package
Bangalore to Tirupati Package Tour
Bangalore to Tirumala Package
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
Oracle Fusion Financials Online Training
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Awesome post……. your article is really informative and helpful for me and other bloggers too
Workday Online Training
Really excellent information and thank you for giving your valuable information
Workday HCM Online Training
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
Oracle Fusion Financials Online Training
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
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
Very interesting blog Thank you for sharing such a nice and interesting blog and really very helpful article
Workday HCM Online Training
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
Oracle Fusion Financials Online Training
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Thank you for sharing such a nice and really very helpful article
Oracle Fusion HCM Online Training
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.
Oracle Fusion HCM Online Training
Nice article thanks for sharing the post...!
Robotic Process Automation Training
SAP ABAP Training
SAP Fico Training
SAP PM Training
SCCM 2012 Training
SCCM 2016 Training
sap abap training
sap bods training
sap bw on hana training
sap fico training
sap grc training
Very interesting blog 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 HCM Online Training
Thank you for sharing such a nice and really very helpful article
Oracle Fusion HCM Online Training
Thank you for sharing such a nice and really very helpful article
Oracle Fusion HCM Online Training
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
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
smart outsourcing solutions is the best outsourcing training
in Dhaka, if you start outsourcing please
visit us: graphic design training
digital marketing training
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
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 HCM Online Training
Very Interesting Blog..Thanks for the article..
Top 5 Data Science Training in Chennai
Data Science Training in velachery
Data Science Training Course Content
Top 5 Data Science Training in Chennai
Best Data Science Training in Chennai
Data Science Certification in Chennai
Data Science Training Institute in Chennai
Data Science Courses in Chennai
Data Science Online Course
Data Science with Python Training in Chennai
Really excellent information and thank you for giving your valuable information
Big Data Hadoop Course | Big Data Hadoop Certification Training
Big Data and Hadoop Online Training | Big Data Hadoop Training
I just loved your article on the beginners guide to starting a blog.If somebody take this blog article seriously in their life, he/she can earn his living by doing blogging.thank you for thizs article. best sap hana online training
It is very good and useful for students and developer. Learned a lot of new things from your post. n Salesforce Training Sydney
You write this post very carefully I think, which is easily understandable to me. Not only this, but another post is also good. As a newbie, this info is really helpful for me. Thanks to you.
Tally ERP 9 Training
tally classes
Tally Training institute in Chennai
Tally course in Chennai
This content of information has
helped me a lot. It is very well explained and easy to understand.
seo training classes
seo training course
seo training institute in chennai
seo training institutes
seo courses in chennai
seo institutes in chennai
seo classes in chennai
seo training center in chennai
Nice blog,Very useful article,Thanks for sharing this information.
Oracle Fusion HCM Online Training
Very good article, very nice information, thanks for sharing.
Regards
Tirupati darshan online booking
Tirupati darshan online booking
Tirupati package from Bangalore
Best Tirupati package from Bangalore
Tirupati darshan package from Bangalore
Tirupati tour package from Bangalore
your site is genuine to view sir
Java Training in Bangalore
Java Training
Java Training in Hyderabad
Java Training in Chennai
Java Training in Coimbatore
wow, i source for the contents so far log but this really creative and very use full contents expected are really cleared and the descriptions are clearly described thank you so much for searching
AWS Course in Bangalore
AWS Course
AWS Certification Course
AWS Certification Training
AWS Online Training
AWS Training
AWS Course in Hyderabad
AWS Course in Coimbatore
Good information.Really super and keep sharing.Thankyou..
Android Training in Bangalore
Android Training
Android Online Training
Android Training in Hyderabad
Android Training in Chennai
Android Training in Coimbatore
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
| Certification | Cyber Security Online Training Course|
Ethical Hacking Training Course in Chennai | Certification | Ethical Hacking Online Training Course|
CCNA Training Course in Chennai | Certification | CCNA Online Training Course|
RPA Robotic Process Automation Training Course in Chennai | Certification | RPA Training Course Chennai|
SEO Training in Chennai | Certification | SEO Online Training Course
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
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
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
Really very helpful article , Thank you for sharing
Data Science Training In Bangalore
Data Science Training
Data Science Online Training
Data Science Training In Hyderabad
Data Science Training In Chennai
Data Science Training In Coimbatore
Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
tally training in chennai
hadoop training in chennai
sap training in chennai
oracle training in chennai
angular js training in chennai
Nice and informative article. amazon web services aws training in chennai
microsoft azure course in chennai
workday course in chennai
android course in chennai
ios course in chennai
nice article
thanks for sharing
data science training in chennai
ccna training in chennai
iot training in chennai
ethical hacking training in chennai
cyber security training in chennai
nice article
thanks for sharing
data science training in chennai
ccna training in chennai
iot training in chennai
ethical hacking training in chennai
cyber security training in chennai
Really useful & keep sharing informative one.
PHP Training in Chennai
PHP Online Training in Chennai
Machine Learning Training in Chennai
iOT Training in Chennai
Blockchain Training in Chennai
Open Stack Training in Chennai