Payday loan very quickly they often decide Emergency Payday Loans Emergency Payday Loans not refusing to new one.

Home > oracle > 10g R2 new feature: Redefine a Partition Online

10g R2 new feature: Redefine a Partition Online

August 31st, 2006

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.

Eagle Fan oracle

  1. June 9th, 2014 at 14:52 | #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. June 11th, 2014 at 19:25 | #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. June 12th, 2014 at 20:09 | #3

    Wow, marvelous weblog layout! How lengthy have you been blogging for? you made blogging glance easy. The overall look of your website is fantastic, as well as the content!

  4. June 13th, 2014 at 00:45 | #4

    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!

  5. June 13th, 2014 at 00:53 | #5

    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.

  6. June 16th, 2014 at 01:09 | #6

    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!

  7. June 23rd, 2014 at 13:46 | #7

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

  8. June 23rd, 2014 at 14:38 | #8

    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?

Comment pages
  1. No trackbacks yet.