Tuesday, October 13, 2009

Using DBMS_REDEFINITION package to reorganize tables online

I needed to partition a table while it was accepting updates. So I decided to use DBMS_REDEFINITION package provide by Oracle.



The following privileges must be granted to the user:
ALTER ANY TABLE
CREATE ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE

The following privileges may be required too:
CREATE ANY INDEX
CREATE ANY TRIGGER

First of all you have to create an interim table with the same columns and data type of the table to be redefined.

Anyway I used SYS user to perform the following steps:
alter session force parallel dml parallel 3;
alter session force parallel query parallel 3;


Next step is to check if table can be redefined
EXEC Dbms_Redefinition.Can_Redef_Table('SMS', 'SMS_TRANSACTION');


With no errors displayed, I can proceed starting the redefintion using the following command. This operation can take quite some time to complete, but any queries and DML are available on table being redefined during the entire process:
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'SMS',
orig_table => 'SMS_TRANSACTION',
int_table => 'INT_SMS_TRANSACTION');
END;


The following command copies dependent objects, so it automatically create any triggers, indexes, materialized view logs, grants, and constraints on our interim table:
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SMS', 'SMS_TRANSACTION','INT_SMS_TRANSACTION',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
dbms_output.put_line(num_errors);
END;


At this point you can query DBA_REDEFINITION_ERRORS view to check for blocking errors. You have to correct these errors and then again reexecute
the COPY_TABLE_DEPENDENTS procedure above:
select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

The next optional step points to resynchronize the interim table before building any constraints and indexes, especially if you think there was delay between the completion of the previous operation and moving on to finish the redefinition.
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'SMS',
orig_table => 'SMS_TRANSACTION',
int_table => 'INT_SMS_TRANSACTION');
END;


Next you can execute the following command to complete the redefinition:
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SMS', 'SMS_TRANSACTION', 'INT_SMS_TRANSACTION');
END;


Finally you should gather new statistic for your "new" table:
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SMS',
tabname => 'SMS_TRANSACTION',
estimate_percent => 45
);
end;

86 comments:

Rahul said...

Hi,

Very usefull article. Thanks for sharing.

Anonymous said...

bxkbokr [url=http://elvalle.elvigia.net/node/8191/]tratament pt slabit[/url] qicpozsy[url=http://www.iarticlezone.com/551/diverticulitis-symptoms-and-natural-diet/]diete de slabit rapide[/url] y
vjikcyi [url=http://www.myboyisanidiot.com/node/3718/]cura de slabire disociata[/url] l lrwylvi[url=http://www.article.neox.in/article.php?id=25327]tratament pt slabit[/url] n
bxqjfep [url=http://moodlequebec.org/tiki-index.php?page=UserPagerefugioher]regim de slabit rapid[/url] o jdmtscr[url=http://sinhvatcanhvn.net/entry104333.html]diete de slabit[/url] l
pcbueuq [url=http://fruitiongames.com/LeonardoTo]dieta disociata[/url] a bjblijs[url=http://www.businessonlineanswers.com/groups/mediterranean-and-beyond-diet-food-list/]cure de slabire rapide[/url] u
qjvzlfl [url=http://www.shpil.com/content/no-carb-diet-what-you-eat-list]dieta disociata[/url] a ohovzqz[url=http://www.bysharing.com/71551/diet-system-for-kidney-condition/]cura de slabire cu mere[/url] c
eqnvzey [url=http://www.webapp4u.com/node/5827/]supa de varza pt slabit[/url] g mmwbwta[url=http://www.alternativedr.com/altdr/groups/a-definite-healthy-type-only-diabetes-diet-nutrition-related-health-chart/]cure de slabire eficiente[/url] c
vzqlhgs [url=http://www.shumma.com.mx/?q=node/89151/]diete pentru slabit[/url] g cjegknn[url=http://www.kochen-mit-freude.de/jillian-michaels-2011-2030-day-shred-diet-program-reviews]metode de slabire rapida[/url] q
mvucqgv [url=http://kanoehk.com/?q=node/33612/]cura de slabire[/url] x prbckfx[url=http://myblog.readforfun.org/Teaching/groups/lose-weight-for-body-write-b-michael-thurmond-in-health-information/]dieta disociata[/url] d
nfbuaxd [url=http://www.newecologyofthings.net/netconnect/AdrianEspa]dieta[/url] n uduqmxy[url=http://www.joshconnormusic.com/caravan/groups/weight-loss-diet-plans-how-which-will-do-it-each-of-our-right-way/]cura de slabire cu mere[/url] m

Anonymous said...

generic xanax 4mg yellow xanax bars - xanax side effects seizures

Anonymous said...

buy tramadol online tramadol withdrawal flu symptoms - tramadol 50 mg kaps

Anonymous said...

buy tramadol online buy cheap tramadol online cod - tramadol hcl for cats

Anonymous said...

buy xanax bars online xanax bars r039 - xanax withdrawal in pregnancy

Anonymous said...

xanax online drug interactions xanax and benadryl - is xanax 1 mg strong

Anonymous said...

buy tramadol online tramadol 50 mg ratiopharm - 3 tramadol high

Anonymous said...

xanax online xanax dosage mri - xanax pills pregnancy

Anonymous said...

buy tramadol overnight delivery tramadol withdrawal leg cramps - tramadol for dogs with food

Anonymous said...

buy tramadol online buy tramadol powder - tramadol addiction stories

Anonymous said...

buy tramadol online tramadol dosage 60 lb dog - tramadol dosage chart for dogs

Anonymous said...

buy tramadol online tramadol dosage rabbits - tramadol 50 mg ketorolaco 10 mg

Anonymous said...

alprazolam without prescription high from xanax bars - does xanax show up on probation drug test

Anonymous said...

buy tramadol online buy tramadol online legit - tramadol 50mg tablets street value

Anonymous said...

xanax order no prescription where to get xanax online - side effects of generic xanax

Anonymous said...

buy tramadol online side effects of tramadol 50mg capsules - tramadol side effects high dose

Anonymous said...

buy tramadol online buy tramadol online with mastercard - tramadol no prescription overnight cod

Anonymous said...

xanax online buy xanax in thailand - buy cheap xanax online with no prescription

Anonymous said...

buy tramadol online buy tramadol with credit card - order tramadol online reviews

Anonymous said...

cheap generic xanax xanax for anxiety in dogs - pristiq xanax drug interactions

Anonymous said...

xanax online xanax withdrawal death - xanax pictures pills generic

Anonymous said...

buy carisoprodol cod carisoprodol 350 mg erowid - soma carisoprodol erowid

Anonymous said...

buy tramadol online citrate 100mg tramadol - high off tramadol 50 mg

Anonymous said...

buy tramadol online tramadol 50 mg looks like - order tramadol echeck

Anonymous said...

carisoprodol soma carisoprodol 350 mg forum - kind drug carisoprodol

Anonymous said...

buy tramadol online tramadol 93 58 side effects - can tramadol for dogs be crushed

Anonymous said...

buy tramadol online no prescription forum buy cheap tramadol - buy tramadol in florida

Anonymous said...

cialis online buy cheapest cialis - buy cialis online new zealand

Anonymous said...

cialis price cheap cialis overnight delivery - cialis buy

Anonymous said...

xanax online much xanax recreational use - order xanax online cheap

Anonymous said...

buy cialis online cialis daily medication - order cialis over the counter

Anonymous said...

xanax online long will 2mg xanax last - xanax pictures pills generic

Anonymous said...

order tadalafil cialis coupon daily - cialis 2.5 price

Anonymous said...

buy cialis online buy cialis online with mastercard - generic cialis next day delivery

Anonymous said...

alprazolam price generic xanax pictures mg - 1mg xanax 5mg valium

Anonymous said...

buy xanax online xanax withdrawal gas - buy prescription drugs online xanax

Anonymous said...

cialis online generic cialis non-prescription - cialis kidney stones

Anonymous said...

cheap cialis 20mg buy cialis 100mg - cialis glaucoma

Anonymous said...

order cialis online 36 hour cialis online - ordering cialis online legal

Anonymous said...

cialis online best price cialis 20 mg - cialis price costco

Anonymous said...

cialis online buy-cialis-generic-online - cialis online ireland

Anonymous said...

cialis no prescription cialis 36 hour pill - buy cheap cialis link online

Anonymous said...

http://landvoicelearning.com/#51602 order tramadol cod saturday delivery - buy tramadol sr 100 mg

Anonymous said...

buy tramadol online 2 50 mg tramadol - buy tramadol dogs usa

Anonymous said...

buy tramadol tramadol 627 - ultram 50 mg oral tablet

Anonymous said...

http://landvoicelearning.com/#30896 tramadol purchase cheap - tramadol 50 mg

Anonymous said...

buy tramadol can you order tramadol online legally - tramadol 50mg vs vicodin

Anonymous said...

order tramadol online cod tramadol 50mg nhs - buy tramadol online europe

Anonymous said...

buy tramadol tramadol webmd - tramadol no prescription online

Anonymous said...

buy tramadol buy tramadol no prescription cod - tramadol overdose yahoo answers

Anonymous said...

http://buytramadolonlinecool.com/#59473 buy tramadol 180 - tramadol online fast

Anonymous said...

buy tramadol online tramadol 50 mg espanol - buy tramadol online cheap no prescription

Anonymous said...

http://buytramadolonlinecool.com/#30807 drug study tramadol generic name - tramadol high how much

Anonymous said...

buy tramadol buy tramadol online overnight delivery - tramadol no prescription free shipping

Anonymous said...

buy tramadol tramadol and ultram recovery room - legal buy tramadol online united states

Anonymous said...

http://buytramadolonlinecool.com/#96430 tramadol 50 mg max dosage - tramadol withdrawal day 6

Anonymous said...

buy ativan online ativan withdrawal onset - ativan oral dose

Anonymous said...

buy tramadol online tramadol hcl 50 mg tab dosage - get rid tramadol withdrawal

Anonymous said...

buy ativan online buy lorazepam online with mastercard - zoloft ativan side effects

Anonymous said...

buy lorazepam online ativan 3 times a day - quitting ativan cold turkey

Anonymous said...

ativan lorazepam lorazepam 1 mg wiki - ativan side effects ask patient

Anonymous said...

cheap alprazolam xanax bars rap song - all kinds xanax pills

Anonymous said...

xanax cost many mg xanax pills - xanax generic vs brand name

Anonymous said...

buy tramadol online tramadol dosage mg/kg - tramadol hcl 50 mg reviews

Anonymous said...

tramadol no rx illegal buy tramadol online usa - tramadol 50mg tablets identification

Anonymous said...

buy tramadol online buy tramadol online cod overnight - buy tramadol online visa

Anonymous said...

buy tramadol online buy tramadol online without prescriptions - buy tramadol hydrochloride

Anonymous said...

http://ranchodelastortugas.com/#51462 xanax tri-score 2mg tablets - pills related to xanax

Anonymous said...

http://bayshorechryslerjeep.com/#7724 xanax drug misuse - xanax withdrawal librium

Anonymous said...

Your post featureѕ eѕtаblіshed helpful to us.
ӏt’s very uѕеful аnԁ you're obviously quite well-informed in this field. You possess opened our sight to various views on this specific subject together with intriquing, notable and reliable written content.

Also visit my site; Xanax
Feel free to visit my website - Xanax

Anonymous said...

Read Full Report long does 50mg tramadol take work - buy tramadol online

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

ugg, hollister, abercrombie and fitch, longchamp, nfl jerseys, lululemon outlet, nike trainers, abercrombie and fitch, soccer shoes, valentino shoes, nike roshe, birkin bag, reebok outlet, insanity workout, instyler, mont blanc, vans shoes, new balance shoes, beats by dre, uggs outlet, ugg australia, giuseppe zanotti, rolex watches, nike roshe run, herve leger, babyliss pro, marc jacobs, barbour, ghd, north face outlet, celine handbags, jimmy choo outlet, asics running shoes, mac cosmetics, north face jackets, bottega veneta, wedding dresses, soccer jerseys, ugg boots, chi flat iron, mcm handbags, p90x, ugg pas cher, nike huarache, ferragamo shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose

Barbarian King said...

Now if you want to order heroine online and earn a good discount on it then Real 24 cart is the right destination for you. Here you can also buy tramadol pills online and get it at the place of your choice.

Buy Ambien 10mg online without prescription with overnight delivery in USA said...

Buy Ambien online
Buy Ambien 10mg online
Buy Ambien online without prescription
Buy Ambien online overnight delivery
Buy Ambien online Cheap

Buy Ambien 10mg online without prescription with overnight delivery in USA said...

Buy Ambien online
Buy Ambien 10mg online
Buy Ambien online overnight delivery
Buy Ambien online without prescription

Buy Tramadol 100mg online without prescription with overnight delivery in USA said...

Buy Tramadol online
Buy Tramadol 100mg online
Buy Tramadol online without prescription
Buy Tramadol online overnight delivery

Buy Tramadol 100mg online without prescription with overnight delivery in USA said...

Buy Tramadol online
Buy Tramadol 100mg online
Buy Tramadol online overnight delivery
Buy Tramadol online without prescription

Buy Ambien 10mg online without prescription with overnight delivery in USA said...

Buy Ambien 10mg online
Buy Ambien online
Buy Ambien online overnight delivery
Buy Ambien online without prescription

Buy Tramadol 100mg online without prescription with overnight delivery in USA said...

Buy Tramadol online
Buy Tramadol 100mg online
Buy Tramadol online overnight delivery
Buy Tramadol online without prescription

Buy Tramadol 100mg online without prescription with overnight delivery in USA said...

Buy Tramadol online
Buy Tramadol 100mg online
Buy Tramadol online overnight delivery
Buy Tramadol online without prescription

Unknown said...

Hi there,

Thank you so much for the post you do and also I like your post, Are you looking for Buy Valium Online in the whole USA? We are providing how to order medicine online, how to contact pharmacy, buy lomotil online, cheapest place to buy vyvanse, Buy vyvanse online, Buy vicodin online, Buy lomotil online, Buy nembutal online from reliable source, nembutal sodium solution, vyvanse for sale, where to buy lomotil online, vicodin side effects, vicodin dosage guide online, trust pharmacy online, Buy from trust medics pharmacy online, Online trust medics pharma, quick tips for buy pharmaceutical products, Buy pharmaceutical products online, pharmacy process flow, pharmaceutical products online, buy medicines online with discount, medicines online with discount, Buying and selling medicine online, online medicine order, medicine home delivery near me, online medicine home delievery near me, independent pharmacy delivery service, prescription courier service, medication delivery companies near me, prescription drugs delivery, prescription delivery service, pharmacy near me,24 hour pharmacy near me now, masks for coronavirus for sale, washable face mask online, best reusable face mask, most comfortable face mask, breathable face mask with filter, anxiety and depression medication list, anxiety medication names with the well price and our services are very fast.
Click here for MORE DETAILS......

CONTACT US: +14248350345
Phone : +1 281 694 4534
Email: mikelkean19@gmail.com


phillipsgrey said...

I need to to thank you for this great read!! I definitely loved every bit of it.
EVERYTHING YOU NEED TO KNOW ABOUT BLUE COOKIES STRAIN I have got you bookmarked to look at new stuff you .

Unknown said...

How to use Tramadol Pills

Tramadol pills 100mg are very easy to consume and can be the quickest way to cure your pains. Tramadol medicines should be consumed every four to six hours per prescription. If you are consuming Tramadol in the form of liquid, it should be taken in precise dosage amounts.

Tramadol-100-mg-90
Tramadol-100-mg-90 Pills
Tramadol-100-mg
Tramadol Medicines
Tramadol Tablets
Tramadol Pills
Call us: 231-221-2887
For more info:https://72hrspills.com/tramadol-100-mg-90/