如何快速安全的更改表的owner

前两天有人问我这个问题,我说你先自己去搜索下答案吧。

后来他说没有找到合适的方法,我搜索了一下,竟然发现没有人提到exchange partition的方法。

exchange partition的方法是最安全,也是最快速的方法。

所以这里写出这种方法供大家参考。

假设有A,B两个用户,我们想把TEST表从A用户移到B用户。

我们以非分区表作为例子:

SQL> conn a/a
Connected.
SQL> create table test(x int);

Table created.

SQL> create index test_idx on test(x);

Index created.

SQL> insert into test select rownum from dual connect by level <10000;

9999 rows created.

SQL> commit;

Commit complete.

SQL> conn b/b
Connected.

SQL> create table temp(x int) partition by range (x)
2 (partition part0 values less than (-1),
3 partition part1 values less than (maxvalue));

Table created.

SQL> create table test(x int);

Table created.

SQL> create index temp_idx on temp(x) local;

Index created.

SQL> create index test_idx on test(x);

Index created.

SQL> alter table temp exchange partition part1 with table a.test including indexes without validation;

Table altered.

SQL> alter table temp exchange partition part1 with table test including indexes without validation;

Table altered.

SQL> select count(*) from a.test;

COUNT(*)
----------
0

SQL> select count(*) from b.test;

COUNT(*)
----------
9999

如果是分区表,操作过程如下:

SQL> conn a/a
Connected.

SQL> create table test(x int) partition by range (x)
2 (partition part0 values less than (100),
3 partition part1 values less than (maxvalue));

Table created.

SQL> create index test_idx on test(x) local;

Index created.

SQL> insert into test select rownum from dual connect by level <1000;

999 rows created.

SQL> commit;

Commit complete.

SQL> conn b/b
Connected.
SQL> create table temp(x int);

Table created.

SQL> create index temp_idx on temp(x);

Index created.

SQL> create table test(x int) partition by range (x)
2 (partition part0 values less than (100),
3 partition part1 values less than (maxvalue));

Table created.

SQL> create index test_idx on test(x) local;

Index created.

SQL> select count(*) from a.test;

COUNT(*)
----------
999

SQL> select count(*) from b.test;

COUNT(*)
----------
0

SQL> alter table a.test exchange partition part0 with table temp including indexes without validation;

Table altered.

SQL> alter table test exchange partition part0 with table temp including indexes without validation;

Table altered.

SQL> select count(*) from a.test;

COUNT(*)
----------
900

SQL> select count(*) from b.test;

COUNT(*)
----------
99

SQL> alter table a.test exchange partition part1 with table temp including indexes without validation;

Table altered.

SQL> alter table test exchange partition part1 with table temp including indexes without validation;

Table altered.

SQL> select count(*) from a.test;

COUNT(*)
----------
0

SQL> select count(*) from b.test;

COUNT(*)
----------
999

复合分区表的情况大同小异,大家可以自己试验一下。

95 thoughts on “如何快速安全的更改表的owner

  1. Does your blog have a contact page? I’m having problems locating it but, I’d like to send you an e-mail. I’ve got some recommendations for your blog you might be interested in hearing. Either way, great website and I look forward to seeing it grow over time.

  2. Hey I am so happy I found your site, I really found you by error, while I was looking on Askjeeve for something else, Anyhow I am here now and would just like to say thanks a lot for a marvelous post and a all round interesting blog (I also love the theme/design), I don’t have time to read it all at the minute but I have book-marked it and also added your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the fantastic job.

  3. Based on my observation, after a foreclosed home is marketed at a sale, it is common for the borrower to still have some sort ofthat remaining balance on the bank loan. There are many financial institutions who seek to have all costs and liens paid by the future buyer. Having said that, depending on a number of programs, restrictions, and state regulations there may be some loans which are not easily fixed through the switch of lending products. Therefore, the duty still falls on the debtor that has had his or her property in foreclosure process. Many thanks sharing your opinions on this weblog.

  4. What’s Happening i am new to this, I stumbled upon this I have found It positively useful and it has helped me out loads. I hope to contribute & aid other users like its aided me. Great job.

  5. I must convey my admiration for your kind-heartedness for persons who need help with in this matter. Your personal commitment to passing the solution around had become surprisingly advantageous and have continually helped many people like me to get to their objectives. The important information implies this much a person like me and still more to my fellow workers. Thanks a lot; from everyone of us.

  6. I needed to create you one little bit of observation in order to thank you yet again for your personal superb tricks you’ve shown in this case. This has been really surprisingly open-handed of you to grant publicly exactly what a lot of folks would have sold as an e-book to end up making some dough for themselves, mostly now that you might have tried it if you ever wanted. Those solutions likewise acted as a great way to know that some people have the same interest just as my very own to find out a whole lot more when it comes to this condition. I believe there are several more fun opportunities up front for those who see your site.

  7. Hi, i read your blog from time to time and i own a similar one and i was just wondering if you get a lot of spam comments? If so how do you prevent it, any plugin or anything you can advise? I get so much lately it’s driving me crazy so any help is very much appreciated.

  8. Another thing I have really noticed is that for many people, less-than-perfect credit is the reaction of circumstances past their control. Such as they may be actually saddled with illness so they really have substantial bills for collections. It could be due to a occupation loss and the inability to do the job. Sometimes divorce proceedings can really send the financial circumstances in the wrong direction. Thank you for sharing your opinions on this site.

  9. Thanks for the guidelines shared on your own blog. One more thing I would like to state is that weight-loss is not information on going on a dietary fad and trying to shed as much weight as possible in a couple of days. The most effective way in losing weight is by using it gradually and using some basic points which can assist you to make the most from the attempt to lose fat. You may understand and be following some tips, however reinforcing knowledge never affects.

  10. Furthermore, i believe that mesothelioma is a exceptional form of many forms of cancer that is commonly found in these previously familiar with asbestos. Cancerous cellular material form while in the mesothelium, which is a shielding lining which covers many of the body’s body organs. These cells usually form in the lining of your lungs, tummy, or the sac that encircles the heart. Thanks for revealing your ideas.

  11. Hey there! Quick question that’s entirely off topic. Do you know how to make your site mobile friendly? My site looks weird when browsing from my iphone4. I’m trying to find a theme or plugin that might be able to resolve this issue. If you have any suggestions, please share. Thanks!

  12. I do enjoy the way you have presented this particular issue and it does indeed present me some fodder for thought. However, through just what I have seen, I simply hope as the actual feedback stack on that individuals continue to be on issue and not start on a tirade regarding some other news of the day. Still, thank you for this exceptional point and even though I do not necessarily go along with this in totality, I regard the perspective.

  13. Hello my loved a single! I wish to say that this post is great, wonderful written and include nearly all crucial infos. I¡¦d prefer to peer a lot more posts like this .

  14. The heart of your writing whilst appearing reasonable originally, did not really settle perfectly with me personally after some time. Someplace within the sentences you managed to make me a believer but only for a very short while. I still have a problem with your leaps in assumptions and you would do well to fill in all those gaps. In the event that you actually can accomplish that, I will definitely be amazed.

  15. I’ve been exploring for a little for any high-quality articles or blog posts in this sort of space . Exploring in Yahoo I eventually stumbled upon this site. Reading this information So i am happy to exhibit that I’ve an incredibly just right uncanny feeling I found out exactly what I needed. I most definitely will make certain to don?t forget this site and give it a look regularly.

  16. Throughout this grand pattern of things you actually receive an A for hard work. Where exactly you misplaced us was first on the details. As as the maxim goes, the devil is in the details… And that could not be much more true right here. Having said that, let me tell you precisely what did do the job. Your writing can be rather persuasive and that is probably the reason why I am making the effort in order to comment. I do not really make it a regular habit of doing that. Second, although I can certainly see a jumps in reasoning you come up with, I am not really certain of how you appear to connect the points which make your final result. For now I will, no doubt subscribe to your point but hope in the foreseeable future you link the facts much better.

  17. Oh my goodness! an incredible article dude. Thank you Nevertheless I’m experiencing issue with ur rss . Don’t know why Unable to subscribe to it. Is there anybody getting equivalent rss problem? Anyone who is aware of kindly respond. Thnkx

  18. Simply desire to say your article is as astonishing. The clearness in your post is just great and I could assume you are an expert on this subject. Fine with your permission let me to grab your RSS feed to keep up to date with forthcoming post. Thanks a million and please continue the enjoyable work.

  19. I am only commenting to let you be aware of of the wonderful encounter my friend’s girl had visiting your webblog. She even learned some pieces, which include what it’s like to possess an ideal helping style to have the rest without problems understand several tricky things. You undoubtedly did more than her desires. I appreciate you for producing such effective, healthy, informative and even unique thoughts on your topic to Kate.

  20. Thank you a lot for giving everyone an extremely wonderful opportunity to read articles and blog posts from this website. It’s always very lovely and also packed with a lot of fun for me personally and my office friends to visit the blog at a minimum three times weekly to learn the new secrets you will have. And indeed, I am also actually fascinated considering the fantastic knowledge you give. Certain 3 facts on this page are unquestionably the finest we have all ever had.

  21. Thanks for your article. What I want to comment on is that when searching for a good on-line electronics retail outlet, look for a website with full information on critical indicators such as the personal privacy statement, safety measures details, any payment guidelines, and other terms in addition to policies. Generally take time to look at help along with FAQ areas to get a far better idea of how the shop performs, what they are capable of doing for you, and how you can make use of the features.

  22. Its like you read my mind! You appear to know so much about this,
    like you wrote the book in it or something. I think that you can do with a few pics
    to drive the message home a bit, but other
    than that, this is wonderful blog. An excellent read.

    I will certainly be back.

  23. Pingback: キヤノン 一眼レフカメラ

  24. I blog frequently so i really many thanks for your website content continuously. These tips have actually peaked my curiosity. I’ll book mark your blog and keep examining for brand spanking new specifics about once weekly. I personally signed up for your own Feed also.

  25. Thanks for another informative blog. The place else could I get that kind of information written in such a perfect means? I’ve a project that I’m simply now working on, and I’ve been on the glance out for such info.

  26. Wonderful site. Lots of useful info here. I¡¦m sending it to several pals ans additionally sharing in delicious. And obviously, thanks to your sweat!

  27. hey there and thank you for your info – I have definitely picked up anything new from right here. I did however expertise a few technical issues using this website, as I experienced to reload the web site lots 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 could damage your high quality score if ads and marketing with Adwords. Anyway I’m adding this RSS to my email and could look out for a lot more of your respective interesting content. Ensure that you update this again soon..

  28. Hello there, just became alert to your blog through Google, and found that it is truly informative. I am gonna watch out for brussels. I’ll be grateful if you continue this in future. Lots of people will be benefited from your writing. Cheers!

  29. Thanks a bunch for sharing this with all folks you really understand what you’re speaking about! Bookmarked. Please also talk over with my web site =). We may have a link change arrangement between us!

  30. Great beat ! I wish to apprentice while you amend your site, how can i subscribe for a blog website? The account helped me a acceptable deal. I had been a little bit acquainted of this your broadcast provided bright clear concept

  31. Most lenders will accept to rollover the cash loan; however, it really is better to check
    with the bank before you decide to sign about the dotted lines Lionel Richie concert if you happen to be looking for any $2,500 loan but you’ve got a low credit
    score, you can’t possibly have a good loan from banks since most banks will
    do a credit check.

  32. I like what you guys are up too. This type of clever work and reporting! Keep up the great works guys I’ve you guys to blogroll.

  33. An impressive share! I have just forwarded this onto a colleague who was conducting a little homework on this. And he in fact bought me dinner because I found it for him lol. So let me reword this. Thank YOU for the meal!! But yeah

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>