Daniel said...Just to say hellow!
Shahid Farooq said...It is good article, I suggest to complete this article by adding other features like split, merge and delete partitions examples.
Rakesh Verma said...Good article
JB said...excellent article, now i have to try on the real table
Derek said...Thanks for this, exactly what I'm needing to do for our prod databases. Very useful!
I can say only one word " WOW"
Thanks a lot ..
Jeff said...Exactly what I was looking for. Complete and concise. Thanks.
Manjit Kumar said...Precise & to the point writing
Peter said...One quirk - you need to recompile all objects referencing 'BIG_TABLE', e.g. with utl_recomp
Aryan said...One query - Can the dbms_redefinition package be used for tables having no Primary Keys.
What will happen to the db_links do we need to create them.If Yes then at what point?
(Provided I'm using Oracle 9i Database)
Adarsh Kumar DBA said...Nice Article , I think Adding Index before the synch is good idea because if you synch before and Add index later the Index may take longer time to build and table will again be out of synch
Mike Alex, said...very Nice article
Tim... said...Building an index on a full table is usually quicker than doing index maintenance while building a table.
Each to their own. :)
Debbie Sams said...It's a great article but I don't know if I can use the info since it doesn't reference a particular Oracle version and it isn't even dated, nor are the comments ????
This method has been available since Oracle 9i. Of course, the simple solution to your question would have been to run the same code. It would have taken you 2 minutes.
joe skinner said...This is one of the best things I have run across in 12 years of being a DBA. Very nice indeed. Would be interested in something changing a hash partitioned table to list partitioned table.
Yuriy said...Why the dbms_redefinition.copy_table_dependents is forgotten?
It wasn't really necessary for the original example. I've added a reference to it now.
Cameron said...Really useful article.
Minor thing but I just noticed you have a few mentions of the word defintion (missing an i)
Wow. It must have been a bad day when I wrote that! :)
I've corrected it now. Thanks for the heads-up. :)
Lalit Kumar B said...Hi Tim,
In the DBMS_REDEFINITION.copy_table_dependents section, there is a missing comma at copy_statistics => FALSE. Please correct it.
Corrected. Thanks for the heads-up! :)
Anonymous said...should be 12c, not work in 11g.
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk
Anonymous said...In the last verification lines, do you think it should be BIG_TABLE2 and not BIG_TABLE?
No. It is correct. If you look earlier I drop BIG_TABLE2. The idea is you are replacing the BIG_TABLE with a new version of BIG_TABLE. The name has to remain consistent.
DO NOT ask technical questions here! They will be deleted!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!
If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.