10g R2 new feature: Redefine a Partition Online

Recently I am working on a project for moving tables from DML to LMT. About 500G data needs to be moved.

Since we don’t have much downtime, we use oracle feature “online redefinition” to move some small tables online.

But same as other oracle features, online redefinition also has its limitation.

When dealing with big tables, it will generate a lot of redo and undo. So in same cases, it’s helpless.

Before oracle 10g, we can only redefine the whole table. You can’t do the online redefinition on partition level.

Oracle 10g R2 allows you to redefine on partition level, you can move one partition at a time.

It’s very helpful to reduce the downtime. I think it would be a very popular 10g new feature.

oracle website gives a sampe for how to do it. I post it here.

Here you have a table named ACCOUNTS with 11 partitions, all in the same tablespace USERS. You want to move them to a new tablespace ACCDATA, which you created specifically for this table. You’ll move the table one partition at a time.

First, create an interim table with the same structure as the table ACCOUNTS but now with data on the ACCDATA tablespace.

SQL> create table accounts_int
2  tablespace accdata
3  as
4  select * from accounts
5  where 1=2
6  /

Note where the partitions are located now:

SQL> select partition_name, tablespace_name, num_rows
2  from user_tab_partitions
3  /

PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ----------
P1                             USERS                                1014
P2                             USERS                                1042
P3                             USERS                                1002
P4                             USERS                                 964
P5                             USERS                                 990
P6                             USERS                                1042
P7                             USERS                                 915
P8                             USERS                                 983
P9                             USERS                                1047
P10                            USERS                                1001
PMAX                           USERS                                   0

11 rows selected.

All partitions are in USERS tablespace. Now move the first partition P1 to the tablespace ACCDATA.

SQL> begin
2     dbms_redefinition.start_redef_table (
3        uname => 'ARUP',
4        orig_table => 'ACCOUNTS',
5        int_table  => 'ACCOUNTS_INT',
6        part_name  => 'P1'
7     );
8  end;
9  /

PL/SQL procedure successfully completed.

Note the line 6, where the part_name parameter specifies the partition to be reorganized. If this parameter is omitted, all the partitions will be redefined at the same time. Now, synchronize the interim table with the original table. (You need to do this only if there are updates going to the table ACCOUNTS.)

SQL> begin
2     dbms_redefinition.sync_interim_table (
3        uname => 'ARUP',
4        orig_table => 'ACCOUNTS',
5        int_table  => 'ACCOUNTS_INT',
6        part_name  => 'P1'
7     );
8  end;
9  /

PL/SQL procedure successfully completed.

Finally, finish the redefinition process.

SQL> begin
2     dbms_redefinition.finish_redef_table (
3        uname => 'ARUP',
4        orig_table => 'ACCOUNTS',
5        int_table  => 'ACCOUNTS_INT',
6        part_name  => 'P1'
7     );
8  end;
9  /

PL/SQL procedure successfully completed.

Confirm the partition P1 was indeed moved to the tablespace ACCDATA.

SQL> select partition_name, tablespace_name, num_rows
2  from user_tab_partitions
3  /

PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ----------
P1                             ACCDATA                              1014
P2                             USERS                                1042
P3                             USERS                                1002
P4                             USERS                                 964
P5                             USERS                                 990
P6                             USERS                                1042
P7                             USERS                                 915
P8                             USERS                                 983
P9                             USERS                                1047
P10                            USERS                                1001
PMAX                           USERS                                   0

11 rows selected.

That’s it; repeat the same procedure for other partitions.

118 thoughts on “10g R2 new feature: Redefine a Partition Online

  1. Whats Going down i’m new to this, I stumbled upon this I’ve discovered It positively helpful and it has helped me out loads. I am hoping to give a contribution

  2. Thank you so much for giving everyone an extraordinarily special opportunity to read critical reviews from this site. It’s always very enjoyable and jam-packed with amusement for me and my office colleagues to search your web site a minimum of thrice in a week to see the newest tips you have. And definitely, we are at all times amazed with all the wonderful tactics you give. Selected 1 areas in this post are particularly the finest we have all had.

  3. I think other site proprietors should take this website as an model, very clean and great user genial style and design, let alone the content. You’re an expert in this topic!

  4. Hi there, You have done an incredible job. I will definitely digg it and personally suggest to my friends. I am sure they’ll be benefited from this site.

  5. Good web site! I really love how it is simple on my eyes and the data are well written. I’m wondering how I could be notified when a new post has been made. I have subscribed to your feed which must do the trick! Have a nice day!

  6. Wonderful paintings! This is the kind of info that are meant to be shared around the web. Shame on the search engines for no longer positioning this submit upper! Come on over and discuss with my website . Thank you =)

  7. Great tremendous things here. I¡¦m very glad to look your article. Thank you so much and i am taking a look ahead to contact you. Will you please drop me a mail?

  8. On June 12, UCLA Health System reported another advancement in the teaching
    of surgical skills around the globe. Attempts to contact both Sergey Brin and Larry Page, the former Stanford University students
    who founded Google in 1998, to request comment were unsuccessful.
    Get Latest Updates On Samsung Nexus S Deals
    and Other Offers Galaxy Tab Deals in UK, Visit onlinemobilephoneshop.

  9. You actually make it appear so easy along with your presentation but I in finding this topic to be actually something which I feel I would never understand. It sort of feels too complicated and very huge for me. I’m having a look forward to your next put up, I’ll try to get the cling of it!

  10. An interesting discussion is worth comment.

    I do believe that you should publish more on this issue,
    it may not be a taboo subject but usually people do not discuss these issues.

    To the next! Cheers!!

  11. Right here is the right site for anybody who really wants to understand this topic. You understand a whole lot its almost tough to argue with you (not that I personally will need toHaHa). You definitely put a brand new spin on a topic that’s been discussed for many years. Great stuff, just great!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>