Partitioning a table online with DBMS_REDEFINITION

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, particulary 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 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.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 orginal 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
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
 (uname=>'ADAM',
 orig_table=>'ORIGINAL',
 int_table=>'INTERIM',
 num_errors=>:num_errors);
END;
/
PL/SQL procedure successfully completed.
SQL> print num_errors
NUM_ERRORS
----------
 0

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;
TABLE_NAME                     PARTITION
------------------------------ ---------
ORIGINAL                       RANGE
SQL> select count(*) from user_tab_partitions;
 COUNT(*)
----------
 100
SQL> select grantee,privilege from  user_tab_privs_made where table_name='ORIGINAL';
GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
HR                             SELECT
SQL> select index_name,table_name from user_indexes;
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
ORIGINAL_ID_IDX                ORIGINAL
TMP$$_ORIGINAL_ID_IDX0         INTERIM

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 :-)

DBMS_REDEFINITION in actionConclusion: 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! :-)

About these ads

, , ,

  1. #1 by mdinh on February 15, 2010 - 20:25

    Would DBMS_REDEFINITION still work if the table being partitioned have multiple FKs and cascading FKs?

    Thanks mdinh.

  2. #2 by Uwe Hesse on February 16, 2010 - 12:45

    Mdinh,
    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. #3 by mdinh on February 17, 2010 - 00:12

    Thanks again.

    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;

    Works Perfectly.

  4. #4 by Geff on February 26, 2010 - 12:24

    Hei Uwe,

    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

    regards
    Geff

  5. #5 by Uwe Hesse on February 27, 2010 - 13:56

    Geff,
    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. #6 by Ruhul on 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. #7 by Uwe Hesse on April 20, 2010 - 07:43

    Ruhul,
    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:

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#i998265

  8. #8 by Joyce on July 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?

    Thank you.

  9. #9 by Uwe Hesse on July 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. #10 by Yalcin YAVAS on January 31, 2012 - 22:13

    Thank but example is not complicated enough for DBA who look for solutions to certain unusual issues.

  11. #11 by Uwe Hesse on February 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. #12 by Michael Fontana on February 2, 2012 - 18:22

    Uwe,

    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.

    Michael Fontana

  13. #13 by Enrique Aviles on February 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. #14 by Uwe Hesse on February 7, 2012 - 13:10

    Michael Fontana,
    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. #15 by Uwe Hesse on February 7, 2012 - 13:13

    Enrique,
    thank you for stopping by and leaving the nice comment! You’re welcome :-)

  16. #16 by Julio M on 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?
    – Julio

  17. #17 by Sudhir Bisht on December 19, 2012 - 07:44

    Hi,

    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.

    Thanks..

  18. #18 by Uwe Hesse on December 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. #19 by Uwe Hesse on December 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. #20 by RN on January 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…

    SQL> begin
    dbms_redefinition.can_redef_table
    (uname=>’NUCLEUS’,
    tname=>’METRIC_VALUES’,
    options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);
    end;
    / 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?

    Thanks,
    Ravi

  21. #21 by RN on January 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. #22 by Uwe Hesse on January 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. #23 by Amit Sanghvi on June 10, 2013 - 16:02

    Thanks Uwe.
    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. #24 by chandu on June 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. #25 by Uwe Hesse on June 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. #26 by Uwe Hesse on June 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. #27 by chandu on June 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. #28 by Uwe Hesse on June 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. #29 by Greg Buc on May 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 (11.2.0.4) environment.

    Thank you again for this!

  30. #30 by Uwe Hesse on May 16, 2014 - 15:02

    Greg, thank you for the feedback! No, there is no 11gR2 pitfall about DBMS_REDEFINITION that I’m aware of.

  1. Blogroll Report 12/02/2009 – 19/02/2010 « Coskan’s Approach to Oracle
  2. Brief Introduction into Partitioning in #Oracle « The Oracle Instructor

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,745 other followers

%d bloggers like this: