If there is a requirement to change the structure of a table that is already in use productively, it may be impossible to get a maintenance downtime for that table, because it is constantly in use. That can be the case for all kind of structural changes of a table, particularly for the change from an ordinary heap table into a partitioned table, which I am going to take here as an example, because I am getting asked frequently in my courses how to achieve it. In order to demonstrate that, I will create a demonstration user with a non-partitioned table with privileges and additional dependent objects on it:
SQL> grant dba to adam identified by adam; Grant succeeded. SQL> connect adam/adam Connected. SQL> create table original as select rownum as id, mod(rownum,5) as channel_id, 5000 as amount_sold, mod (rownum,1000) as cust_id, sysdate as time_id from dual connect by level<=1e6; Table created. SQL> create index original_id_idx on original(id) nologging; Index created. SQL> grant select on original to hr; Grant succeeded.
The challenge is now to change this table into a partitioned one while it is used with DML & queries by end users. For this purpose, we introduced already in 9i (if I recall it right) the package DBMS_REDEFINITION. First step would be to ask, whether it can be used in this case:
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - Production PL/SQL Release 22.214.171.124.0 - Production CORE 126.96.36.199.0 Production TNS for Linux: Version 188.8.131.52.0 - Production NLSRTL Version 184.108.40.206.0 - Production
SQL> begin dbms_redefinition.can_redef_table (uname=>'ADAM', tname=>'ORIGINAL', options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID); end; /
PL/SQL procedure successfully completed.
Because there is no Primary Key on the original table, I have to use CONS_USE_ROWID, else I could use CONS_USE_PK. There are no objections against the online redefinition of the table here – else an error message would appear. Next step is to create an interim table of the structure, desired for the original table. In my case, I create it interval partitioned (an 11g New Feature). I could also change storage attributes and add or remove columns during that process.
SQL> create table interim (id number, channel_id number(1), amount_sold number(4), cust_id number(4), time_id date) partition by range (cust_id) interval (10) (partition p1 values less than (10)); Table created.
My original table has 1000 distinct cust_ids, so this will lead to 100 partitions – each partion will contain 10 distinct cust_ids. One benefit of that would be the possibility of partition pruning, should there be statements, specifying the cust_id in the where-condition. These statements will be about 100 times faster as a full table scan. The next step will basically insert all the rows from the original table into the interim table (thereby automatically generating 99 partitions), while DML during that period is recorded:
SQL> set timing on SQL> BEGIN DBMS_REDEFINITION.START_REDEF_TABLE (uname=>'ADAM', orig_table=>'ORIGINAL', int_table=>'INTERIM', options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID); end; / PL/SQL procedure successfully completed. Elapsed: 00:00:22.76
If this step takes a long time to run it might be beneficial to use the SYNC_INTERIM_TABLE procedure occasionally from another session. That prevents a longer locking time for the last step, the calling of FINISH_REDEF_TABLE. Next step is now to add the dependent objects/privileges to the interim table:
SQL> set timing off
SQL> vari num_errors number
PL/SQL procedure successfully completed.
SQL> print num_errors
There was no problem with this step. Until now the original table is still an ordinary heap table – only the interim table is partitioned:
SQL> select table_name from user_part_tables; TABLE_NAME ------------------------------ INTERIM
In the last step, the two tables change their names and the recorded DML that occured in the meantime gets used for actualization:
SQL> begin dbms_redefinition.finish_redef_table (uname=>'ADAM', orig_table=>'ORIGINAL', int_table=>'INTERIM'); end; / PL/SQL procedure successfully completed.
We will now determine that the original table is partitioned and the dependencies are still there:
SQL> select table_name,partitioning_type from user_part_tables;
SQL> select count(*) from user_tab_partitions;
SQL> select grantee,privilege from user_tab_privs_made where table_name='ORIGINAL';
SQL> select index_name,table_name from user_indexes;
The interim table can now be dropped. We changed the table into a partitioned table without any end user noticing it!
This picture illustrates the steps you have seen above – hope you find it useful 🙂
Conclusion: If a table structure needs to be modified and the table is permanently accessed by end users, this can be done with some effort using DBMS_REFDEFINITION. One common – but not the only possible – use case is the modification of a non-partitioned table into a partitioned one. You have seen a simplified demonstration about it. As always: Don’t believe it, test it! 🙂
Popular on https://t.co/jpLJM0RkaU: Partition a table with DBMS_REDEFINITION https://t.co/rOMDau6xW1 #Oracle pic.twitter.com/jIof4fwF8x
— Uwe Hesse (@UweHesse) January 29, 2016
#1 von mdinh am Februar 15, 2010 - 20:25
Would DBMS_REDEFINITION still work if the table being partitioned have multiple FKs and cascading FKs?
#2 von Uwe Hesse am Februar 16, 2010 - 12:45
it should work the same. I have just tested it with a foreign key from the original table. It was still there and working after the redefinition. By the way: You could check that out yourself easy with the given example, couldn’t you? That’s the good thing with examples – or call them test cases 🙂
#3 von mdinh am Februar 17, 2010 - 00:12
What I did was to create ORIGINAL_PARENT and ORIGINAL_CHILD. After the redefinition, there were TMP constraints from / to INTERIM. Solved that by – drop table interim cascade constraint purge;
#4 von Geff am Februar 26, 2010 - 12:24
does this not still have the disadvantage of requiring double the storage space for the time that both tables exist?
Which in many cases would be a large obstacle
#5 von Uwe Hesse am Februar 27, 2010 - 13:56
yes that seems to be not avoidable. It’s like a „Create Table as Select“, but with the additional benefit that you can do it while users are permanently modifying the original table.
#6 von Ruhul am April 19, 2010 - 14:13
Please let us know, does is work on oracle 10g database version ? if possible please give us some example.
#7 von Uwe Hesse am April 20, 2010 - 07:43
it should work pretty much the same with 10g, since the package and all the procedures are there already. Don’t have the time right now nor a 10g DB at hand to give an example, but you may test that yourself and tell us the outcome, if you like? Check out the 10g Online Documentation for it:
#8 von Joyce am Juli 6, 2010 - 17:35
We have a table that is incorrectly range partitioned and hash sub-partitioned and need to repartition only by range partitions.
The original table has a PK and the interim table has also been defined with a PK.
The interim table needs to be defined with the correct range partitions, but these have to be different names, correct?
Then, are these partitions just renamed after the redefinition?
There are also CHECK constrants and FKs on the original table.
How are these handled?
#9 von Uwe Hesse am Juli 8, 2010 - 16:09
Joyce, I presently don’t have the time to answer that. This is not intended as a forum, after all. Please try OTN or AskTom with that kind of questions. See my list of Links above at the right. Thank you for your understanding.
#10 von Yalcin YAVAS am Januar 31, 2012 - 22:13
Thank but example is not complicated enough for DBA who look for solutions to certain unusual issues.
#11 von Uwe Hesse am Februar 1, 2012 - 09:40
Thanks for stopping by! Good luck with searching the web to find the solution that exactly matches your ‚unusual issues‘ 🙂
#12 von Michael Fontana am Februar 2, 2012 - 18:22
This is an interesting approach. I have used dbms_redefinition, but never in this manner.
I am researching a client request to convert an extremely large table into a partitioned one. Is this approach reasonable in an VLDB environment (7+ terabytes)?
You allude to running SYNC_INTERIM_TABLE from another session without explaining the procedure, the reasons to use it, and what it would accomplish.
Any assistance or comment you can provide would be appreciated.
#13 von Enrique Aviles am Februar 5, 2012 - 23:16
I’m glad I found this example because I need to go the other way around, from a partitioned table to an unpartitioned heap organized table. I’ll try it at work tomorrow. Thanks for the info.
#14 von Uwe Hesse am Februar 7, 2012 - 13:10
I think that I mentioned briefly why to use sync_interim_table in case. Anyway: Basically, start_redef will trigger an insert into interim table select * from original table, capturing all DML that is done during that process into MV logs.
In other words: The longer the insert takes and the more DML is done during that, the more delta is stored into the MV logs. The final step finish_redef (applying all the delta) takes accordingly long – unless you call sync_interim in the meantime, which will speed up the final call of finish_redef.
#15 von Uwe Hesse am Februar 7, 2012 - 13:13
thank you for stopping by and leaving the nice comment! You’re welcome 🙂
#16 von Julio M am August 3, 2012 - 22:24
Thank you for this great example. I follow it and it worked great like a charm. I think you made have a typo on this line „select grantee,privilege from user_tab_privs_made where table_name=’ORIGINAL‘;“
should this be user_tab_privs . Also have you use partition exchange? if so witch method do you prefer?
#17 von Sudhir Bisht am Dezember 19, 2012 - 07:44
Great way of achieving it. But if I am not able to redefine a table. Then what do I do.
As I get :
*ORA-12090: cannot online redefine table *
I am trying to redefine an Interval Partitioned Table.
#18 von Uwe Hesse am Dezember 19, 2012 - 09:56
Julio, you’re welcome! It is not a typo, but works exactly as in the posting – I have just checked it again. user_tab_privs_made is the right view: http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_5474.htm#i1635564
#19 von Uwe Hesse am Dezember 19, 2012 - 10:01
Sudhir Bisht, your table violates one of the conditions, outlined in the Error Messages documentation here: http://docs.oracle.com/cd/E11882_01/server.112/e17766/e9858.htm#sthref3044 (or it belongs to sys or system, which is not listed there).
Either remove that condition or transform it manually without dbms_redefinition, which requires a maintenance window without user activity on the original table.
#20 von RN am Januar 9, 2013 - 02:05
Hello – I have a 11gr2 table that has Fkeys and not PKeys and the check from Oracle doc fails.
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(‚NUCLEUS‘, ‚METRIC_VALUES‘);
BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(‚NUCLEUS‘, ‚METRIC_VALUES‘); END;
ERROR at line 1:
ORA-12089: cannot online redefine table „NUCLEUS“.“METRIC_VALUES“ with no primary key
ORA-06512: at „SYS.DBMS_REDEFINITION“, line 139
ORA-06512: at „SYS.DBMS_REDEFINITION“, line 1783
ORA-06512: at line 1
Luckily I came across your link…
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
So can I proceed with the DBMS_REDEFINITION? Any other gotchas or stuff i need to check? Do you have any other useful material on roll ups from daily to weekly to monthly?
#21 von RN am Januar 9, 2013 - 02:08
One other thing I wanted to add was.. my original table is already partitioned and at some point monthly partitions were stopped and I have been tasked with cleaning up the non-partitioned data back into partitions and the rolls ups… I plan to precreate the partitions that were missed and hopefully that will put the dates in correct partitions. Any other inputs?
#22 von Uwe Hesse am Januar 9, 2013 - 12:05
Ravi, yes, you can go on now with the redefinition. Check out http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables007.htm#ADMIN11668 if you haven’t done already. My recommendation is also that you post in the OTN Database forum http://forums.oracle.com/forums/forum.jspa?forumID=61&start=0 – lots of more eyes and experience there to help you.
#23 von Amit Sanghvi am Juni 10, 2013 - 16:02
Though I have used this feature in the past, its always great to be able to refresh it and that too in such an enjoyable and simple way.
I’m a BIG fan of your blog. Keep up the great work.
If you are ever in London, It’ll be a privilege to meet up over a coffee.
#24 von chandu am Juni 12, 2013 - 21:09
ORA-01442: column to be modified to NOT NULL is already NOT NULL
( when i am doing copy table dependent, above error getting and this is removing primary key constraint and adding as unique index on same column) — pls suggest how to resolve
#25 von Uwe Hesse am Juni 13, 2013 - 10:14
Amit, thank you for the nice feedback & offer! I may get back to you on that, should I travel to London again 🙂
#26 von Uwe Hesse am Juni 13, 2013 - 10:18
Chandu, you may try ignore_errors=>true as listed here: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_redefi.htm#i999656
#27 von chandu am Juni 13, 2013 - 13:23
Hi Uwe, thank you very much… now it is resolved. and again i am getting below error after done partition..
Error 2: ORA-14402: updating partition key column would cause a partition change
— when user trying to update, it is getting this error even there is enough partition key column range. ( once if enable row movement, it is correctly updating and not getting error)
In production, if enable row movement.. any performance issue will occur?? any alternate solution for this?? pls help…thanks in advance…
#28 von Uwe Hesse am Juni 13, 2013 - 14:55
It is a bit unusual to have updates on partition key columns. This causes possibly the movement of rows outside their present partition into another, which does more I/O than an ordinary update and also changes the RowID. The enabling of row movement does no harm in itself, besides allowing those updates. The alternate solution would be to avoid those updates or to pick another column for the partition key.
#29 von Greg Buc am Mai 15, 2014 - 23:34
Yours is the most comprehensive example on this subject. We have a 375-million record table, multi-indexed with multi-constraints that needs to be partitioned by Quarter and then month with minimal (zero) downtime; your test case above proved the concept. I will modify the scripts to use our „real“ scenario (testing on non-production first, of course). 🙂
Q: It has been a a couple years since you posted this: any issues with 11g?
I have compared your example with latest 11gR2 documentation and it looks fine. Yours also ran fine in our 11gR2 (220.127.116.11) environment.
Thank you again for this!
#30 von Uwe Hesse am Mai 16, 2014 - 15:02
Greg, thank you for the feedback! No, there is no 11gR2 pitfall about DBMS_REDEFINITION that I’m aware of.
#31 von Karthik am November 4, 2014 - 07:30
Excellent explanation. Any chance the redef procedure hits ora-1555 error as data can be updated in the master table during online redifinition?
#32 von Uwe Hesse am November 5, 2014 - 17:52
We don’t need undo for the sync when finish_redef_table is done. Instead, an MV log captures the modifications. Therefore: I don’t think so.
#33 von Rui Queirós am Januar 8, 2015 - 15:01
I tried using this method, but I’m loosing the not null information for the table.
I have a table a:
B not null
E not null
I create a tmp table b with partitions:
But in the end, my table A has the partition information, but it does not have the not null information.
#34 von jks am Juli 2, 2015 - 17:43
How can I un-partition a partitioned table?
#35 von Nelson Calero am April 10, 2017 - 22:56
This is a reply for Rui to have it as reference if somebody else finds the same issue:
that is caused by a known bug:4396234 ET10.2OREDEF: NULLABLE COL OF *_TAB_COLUMNS TABLE NOT UPDATED AFTER ONLINE REDEF
You need to manually change those columns to be not null after redefinition completes.
#36 von Eric Yen am Juni 3, 2017 - 01:05
Thank you for sharing and such active discussion
#37 von Ravin Maharaj am Oktober 7, 2017 - 05:21
Please share your views on how to partition an index online. An existing b-tree index needs to be converted to a global hash partitioned index and no downtime is allowed
#38 von Uwe Hesse am Oktober 31, 2017 - 09:05
If you are on 12c, you can create the partitioned index invisible while the old index is still in use. Then make the old invisible (or drop, if you are confident the new is better) and make the new visible.