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. July 21st, 2013 at 18:44 | #1

    I consider other web-site proprietors ought to take this web site as an model, pretty clean and fantastic user friendly style and style, let alone the content material. You might be an expert within this topic!

  2. July 23rd, 2013 at 14:49 | #2

    You definitely should take portion within a contest for starters of your finest blogs over the online world. I’m going to advocate this page!

  3. July 30th, 2013 at 02:39 | #3

    I actually wanted to send a small remark so as to say thanks to you for these lovely tips you are placing here. My long internet investigation has finally been paid with good quality ideas to share with my pals. I would declare that most of us website visitors are unequivocally fortunate to exist in a wonderful network with many awesome people with very beneficial opinions. I feel truly fortunate to have discovered your web pages and look forward to tons of more fun minutes reading here. Thanks a lot again for a lot of things.

  4. July 30th, 2013 at 07:10 | #4

    hello there and thank you for your information – I have certainly picked up anything new from right here. I did however expertise some technical points using this site, since I experienced to reload the site lots of times previous to I could get it to load correctly. I had been wondering if your hosting is OK? Not that I am complaining, but sluggish loading instances times will very frequently affect your placement in google and could damage your quality score if ads and marketing with Adwords. Well I am adding this RSS to my e-mail and could look out for much more of your respective intriguing content. Make sure you update this again soon..

  5. July 31st, 2013 at 03:18 | #5

    hey there and thank you for your info – I have definitely picked up something new from right here. I did however expertise a few technical issues using this website, as I experienced to reload the website a lot of times previous to I could get it to load correctly. I had been wondering if your web host is OK? Not that I am complaining, but slow loading instances times will very frequently affect your placement in google and can damage your high quality score if ads and marketing with Adwords. Well I’m adding this RSS to my email and could look out for much more of your respective intriguing content. Ensure that you update this again soon..

  6. July 31st, 2013 at 05:14 | #6

    I am also commenting to make you know what a incredible encounter my princess experienced studying your web page. She even learned a good number of pieces, including how it is like to possess an incredible giving spirit to get other people really easily thoroughly grasp chosen problematic matters. You really did more than visitors’ expectations. Many thanks for producing such essential, dependable, revealing as well as fun thoughts on your topic to Julie.

  7. August 1st, 2013 at 11:06 | #7

    Your blog is astounding dude. i adore to take a look at it daily. extremely good layout and content material ,

  8. August 2nd, 2013 at 23:44 | #8

    Do you individuals have a myspace fan page? I looked for for 1 on twitter but could not uncover it, I’d definitely prefer to grow to be a fan!

  9. August 3rd, 2013 at 02:51 | #9

    Thank you for some other great post. The place else could anyone get that kind of information in such a perfect method of writing? I have a presentation next week, and I am at the look for such info.

  10. August 3rd, 2013 at 11:11 | #10

    I intendedI neededI wantedNeeded to composecreatedraftpostputsendwrite you aonethatthethis bit oflittlelittle bit oftinyvery littlevery small noteobservationremarkword in order tojust toso as tototo be able toto finallyto help give many thanksgive thankssay thank yousay thankssay thanks a lotthank youthank you so muchthank you very much againas beforeonce againover againthe moment againyet again about theconsidering thefor all thefor thefor thesefor thosefor yourfor your personaljust for theon theon yourover theregarding therelating to thewith thewith your amazingawesomebeautifulbreathtakingexceptionalextraordinaryfantasticgorgeousgreatincrediblelovelymagnificentmarvelousnicepleasantpleasingpreciousprettyremarkablespectacularsplendidstrikingstunningsuperbuniquewonderful advicebasicsconceptsguidelinesideasinformationknowledgemethodsopinionspointersprinciplessecretssolutionsstrategiessuggestionstacticstechniquesthingsthoughtstipstricksviews you’veyou have contributeddiscusseddocumentedfeaturedprovidedsharedshown aboveat this timeherein this articlein this caseon this pageon this siteon this website. It’sIt has beenIt isIt wasThis has beenThis is certainlyquitereallysimplyso extremelyincrediblyparticularlyprettyremarkablyseriouslyshockinglystrangelysurprisinglytremendouslyunbelievablywonderfully generousopen-handed ofwith people like youyou givingin givingto allowto conveyto deliverto giveto grantto maketo offerto presentto provideto supply easilyextensivelyfreelyopenlypubliclyunhamperedunreservedlywithout restraint allall thatexactly whatjust whatprecisely whatwhatwhat exactly a few individualsa few peoplea lot of folksa lot of peoplea number of peoplea number of usmany individualsmany of usmany peoplemost of usmost peoplenumerous peoplesome people could havecould possibly havemight havewould’vewould have advertiseddistributedmade availablemarketedofferedoffered for salesoldsupplied as anfor an e-booke bookebookelectronic book in makingin order to maketo earnto end up makingto generateto getto help maketo help with makingto make some buckssome cashsome doughsome moneysome profit for their own endfor themselveson their own, and in particularcertainlychieflyespeciallyeven more somost importantlymost notablymostlynotablyparticularlypreciselyprimarilyprincipallyspecifically considering thatconsidering the fact thatgiven thatnow thatseeing thatsince you could havecould possibly havemight havemight well have done ittried it if youif you everin case youin the event you considered necessarydecideddesiredwanted. TheTheseThose advicebasicsconceptscreative ideasgood ideasgood tipsguidelinesideasinspiring ideaspointerspointsprinciplessecretssmart ideassolutionsstrategiessuggestionstacticstechniquesthingsthoughtstipstricks additionallyalsoas wellin additionlikewise actedservedworked as aas thelike alike theto be ato be theto becometo become ato provide a easy way tofantastic way togood way togreat way to be aware thatbe certain thatbe sure thatcomprehendfully graspfully grasp thatknow thatrealize thatrecognize thatunderstand that many peoplemost peopleother individualsother peopleother people onlinesome peoplesomeone elsethe rest have a similarhave similarhave the identicalhave the same desiredreamdreamseagernessfervorinterestkeennesspassionzeal just asjust likelikemuch likereally likesimilar tothe same as minemy ownmy personal ownmy very own to figure outto find outto graspto knowto know the truthto learnto realizeto seeto understand a good deal morea great deal morea little morea lot morea whole lot moregood deal moregreat deal morelots moremany moremoremore and moremuch moresignificantly moresomewhat morevery much moreway morewhole lot more aroundconcerningin regard toin respect ofin terms ofon the subject ofon the topic ofpertaining toregardingrelated towhen consideringwhen it comes towith reference towith regard towith regards towith respect to this conditionthis issuethis matterthis problem. CertainlyI’m certainI’m sureI am certainI am sureI believeI knowI think there are a lotthere are lots ofthere are manythere are millions ofthere are numerousthere are severalthere are somethere are thousands of more enjoyablemore funmore pleasantmore pleasurable instancesmomentsoccasionsopportunitiesperiodssessionssituationstimes aheadin the futureup front for folks whofor individuals thatfor individuals whofor many whofor people whofor those who browse throughcheck outdiscoverexaminefind outgo throughlook intolook overlooked atlooked overreadread carefullyread throughscanscan throughseestart readingtake a look atview your blogyour blog postyour siteyour website.

  11. August 4th, 2013 at 12:10 | #11

    This can be a terrific internet page, could you be involved in performing an interview about how you created it? If so e-mail me!

  12. August 9th, 2013 at 23:16 | #12

    This is very interesting, You’re a very skilled blogger. I’ve joined your feed and look forward to seeking more of your wonderful post. Also, I’ve shared your web site in my social networks!

  13. August 11th, 2013 at 14:32 | #13

    This really is precisely what I had been seeking for, thank you

  14. August 11th, 2013 at 19:33 | #14

    You made some good points there. I looked on the internet for the topic and found most individuals will approve with your website.

  15. August 23rd, 2013 at 17:28 | #15

    Superb, what a web position it is! This blog presents practical statistics to us, be it up.

  16. September 2nd, 2013 at 22:59 | #16

    Hey there would you mind letting me know which web host you’re utilizing? I’ve loaded your blog in 3 completely different internet browsers and I must say this blog loads a lot quicker then most. Can you suggest a good hosting provider at a reasonable price? Kudos, I appreciate it!

  17. September 5th, 2013 at 05:43 | #17

    Howdy! Do you know if they make any plugins to assist with Search Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good results. If you know of any please share. Appreciate it!

  18. September 5th, 2013 at 15:01 | #18

    I have mastered approximately in mint condition things from your web locate on the subject of delicate computers. One more thing I have always assumed is that computer systems have turn into a product that apiece residence essential have for a lot of reasons. They supply you with convenient customs in which to arrange the inland, give bills, exit shopping, learning, tune in to music and in scores of belongings watch tube series. An innovative practice to all-embracing on the whole of these tasks is a computer. These desktops are portable ones, minute, robust and convenient.

  19. September 6th, 2013 at 06:27 | #19

    you are really a good webmaster. The web site loading speed is incredible. It seems that you are doing any unique trick. Moreover, The contents are masterwork. you’ve done a magnificent job on this topic!

  20. September 6th, 2013 at 19:18 | #20

    I know of the fact that currently, more and more people are being attracted to cameras and the field of picture taking. However, like a photographer, you will need to first spend so much time period deciding the model of photographic camera to buy and also moving out of store to store just so you could buy the least expensive camera of the brand you have decided to settle on. But it doesn’t end at this time there. You also have to take into consideration whether you should obtain a digital video camera extended warranty. Thx for the good ideas I acquired from your website.

  21. September 7th, 2013 at 00:27 | #21

    I was just searching for this info for some time. After six hours of continuous Googleing, finally I got it in your web site. I wonder what’s the lack of Google strategy that don’t rank this kind of informative websites in top of the list. Usually the top web sites are full of garbage.

  22. September 7th, 2013 at 02:44 | #22

    Wow, incredible blog structure! How long have you been running a blog for? you made blogging glance easy. The overall look of your web site is fantastic, let alone the content material!

  23. September 7th, 2013 at 05:17 | #23

    Thanks for the several tips shared on this site. I have observed that many insurers offer consumers generous reductions if they elect to insure many cars with them. A significant volume of households own several automobiles these days, in particular those with elderly teenage kids still dwelling at home, as well as savings on policies can certainly soon begin. So it pays to look for a good deal.

  24. September 7th, 2013 at 10:42 | #24

    I cherished as much as you will obtain carried out right here. The comic strip is tasteful, your authored subject matter stylish. nevertheless, you command get bought an shakiness over that you would like be turning in the following. sick for sure come further previously once more since precisely the same just about a lot incessantly within case you defend this increase.

  25. September 9th, 2013 at 15:11 | #25

    I simply wished to appreciate you once more. I am not sure the things I might have gone through in the absence of the recommendations shown by you concerning that situation. It became the scary concern for me personally, but looking at your specialised style you resolved the issue took me to jump with contentment. I’m thankful for the assistance and as well , sincerely hope you find out what an amazing job you have been carrying out educating people today by way of your blog. I’m certain you’ve never met all of us.

  26. September 10th, 2013 at 02:51 | #26

    I haven’t checked in here for a while because I thought it was getting boring, but the last few posts are great quality so I guess I’ll add you back to my daily bloglist. You deserve it my friend )

  27. September 10th, 2013 at 11:33 | #27

    Wonderful goods from you, man. I have bear in mind your stuff prior to and you are simply extremely magnificent. I actually like what you’ve obtained here, certainly like what you are stating and the best way by which you are saying it. You are making it entertaining and you continue to care for to stay it sensible. I can’t wait to learn much more from you. That is really a terrific website.

  28. October 1st, 2013 at 23:20 | #28

    Great weblog here! Additionally your web site rather a lot up fast! What web host are you the usage of? Can I am getting your affiliate hyperlink for your host? I desire my website loaded up as fast as yours lol

  29. October 1st, 2013 at 23:59 | #29

    whoah this blog is great i love reading your articles. Keep up the great work! You know, many people are searching around for this information, you could help them greatly.

  30. October 2nd, 2013 at 07:36 | #30

    I have learned several important things by means of your post. I’d also like to express that there might be situation that you will get a loan and don’t need a co-signer such as a National Student Aid Loan. However, if you are getting that loan through a classic financier then you need to be able to have a cosigner ready to make it easier for you. The lenders may base that decision on a few components but the most important will be your credit standing. There are some loan companies that will additionally look at your job history and make up your mind based on this but in almost all cases it will be based on on your score.

  31. October 2nd, 2013 at 21:18 | #31

    Hello there, I found your site via Google whilst searching for a similar matter, your web site got here up, it looks great. I’ve bookmarked it in my google bookmarks.

  32. January 7th, 2014 at 00:54 | #32

    Great blog right here! Also your web site a lot up fast! What host are you the use of? Can I get your associate hyperlink to your host? I wish my website loaded up as quickly as yours lol

  33. January 28th, 2014 at 16:12 | #33

    My husband and i have been happy Michael could do his investigation by way of the ideas he had out of the web pages. It is now and again perplexing just to happen to be making a gift of guides which often a number of people might have been making money from. And we all fully grasp we have the blog owner to give thanks to for this. The type of illustrations you made, the straightforward blog menu, the relationships you can help to instill it is many awesome, and it is letting our son and the family understand the subject matter is satisfying, which is seriously important. Thanks for everything!

  34. April 25th, 2014 at 05:46 | #34

    Your style is very unique compared to other people I’ve read stuff from. Thank you for posting when you’ve got the opportunity, Guess I will just bookmark this page.

Comment pages
  1. No trackbacks yet.