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;

128 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...

xanax online xanax pills cost - valium or xanax for anxiety

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...

best place to buy xanax online blue xanax bars 2mg - order xanax online canada no prescription

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...

buy tramadol online tramadol 200mg online - tramadol hcl 50 mg how many can i take

Anonymous said...

buy carisoprodol carisoprodol half life - buy carisoprodol no prescription

Anonymous said...

can you buy xanax online legally generic xanax 029 - xanax side effects addiction

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...

carisoprodol 350 mg carisoprodol name brand - carisoprodol 749

Anonymous said...

carisoprodol 350 mg carisoprodol sleep - vicodin online no prescription needed

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...

carisoprodol 350 mg carisoprodol 350 mg get you high - buy carisoprodol codeine

Anonymous said...

xanax online xanax withdrawal death - xanax pictures pills generic

Anonymous said...

buy carisoprodol carisoprodol dosage back pain - there drug test carisoprodol

Anonymous said...

carisoprodol 350 mg carisoprodol 350 mg strength - carisoprodol 350 mg strength

Anonymous said...

xanax online buying xanax online safe - can you buy xanax online legally

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 cialis online cheap cialis with prescription - cialis daily effectiveness

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...

cheap xanax no prescription buy xanax bars online no prescription - xanax wiki fr

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...

cialis online no prescription buy cialis online in usa - cheap cialis overnight delivery

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 cialis price in thailand - cialis daily vs. 36 hr

Anonymous said...

cialis online generic cialis mens health - buy cialis online germany

Anonymous said...

xanax online xanax vs klonopin high - xanax meps drug test

Anonymous said...

buy cialis online should you buy cialis online - cialis recommended dosage

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...

xanax online 2mg klonopin vs xanax bar - xanax pill sizes

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...

buy cialis online order cialis safely online - how buy cialis online

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...

buy tramadol online buy tramadol online from usa - tramadol addiction after 1 week

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...

buy tramadol tablets tramadol with vicodin - can you buy tramadol in greece

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...

buy tramadol tramadol 50 mg for ultram - tramadol hcl interactions

Anonymous said...

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

Anonymous said...

http://landvoicelearning.com/#44827 tramadol hcl 650 - tramadol hcl para que serve

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 buy tramadol in usa - is tramadol for dogs an nsaid

Anonymous said...

buy tramadol usa tramadol withdrawal after short term use - tramadol for dogs dosage usa

Anonymous said...

buy tramadol klonopin and tramadol high - tramadol 750 mg

Anonymous said...

buy tramadol rx cheap tramadol no prescription needed - high off tramadol 50 mg

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 tramadol tramadol overdose medscape - does tramadol 50 mg do you

Anonymous said...

learn how to buy tramdadol tramadol addiction and withdrawal symptoms - tramadol 100mg dogs

Anonymous said...

learn how to buy tramdadol tramadol efectos secundarios - tramadol for dogs vs rimadyl

Anonymous said...

http://blog.dawn.com/dblog/buy/#fda tramadol overdose info - buy tramadol online no rx

Anonymous said...

buy ativan online yellow ativan dosage - ativan withdrawal wikipedia

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 tramadol tramadol 50 mg for humans - buytramadol.ca

Anonymous said...

buy ativan online lorazepam to buy online - ativan cost

Anonymous said...

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

Anonymous said...

tramadol online online us pharmacy no prescription tramadol - tramadol online bluebook

Anonymous said...

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

Anonymous said...

http://reidmoody.com/#54126 dosage of ativan for sleep - ativan drug addiction

Anonymous said...

ways to buy ativan online lorazepam 1mg pics - lorazepam 1mg que es

Anonymous said...

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

Anonymous said...

buy xanax online xanax 2mg dosage - xanax generic xanax

Anonymous said...

cheap alprazolam xanax bars rap song - all kinds xanax pills

Anonymous said...

buy xanax online generic xanax 11 - much 1mg xanax worth

Anonymous said...

http://staam.org/#74560 legal buy tramadol online - tramadol 50mg tablets info

Anonymous said...

http://ranchodelastortugas.com/#58720 xanax effects anxiety - xanax 3 side effects

Anonymous said...

buy xanax online xanax side effects - xanax bars how much to take

Anonymous said...

buy tramadol online order tramadol online cod overnight - generic brand name tramadol

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...

http://ranchodelastortugas.com/#37284 xanax side effects vision - buy alprazolam pills

Anonymous said...

buy xanax online 1mg klonopin compared xanax - xanax xr no prescription

Anonymous said...

order tramadol online overnight buy tramadol online legit - tramadol for dogs after neutering

Anonymous said...

buy xanax online buy xanax online reviews - xanax bars drug info

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...

buy xanax online xanax street value - does xanax show up same klonopin drug test

Anonymous said...

buy xanax online 2mg xanax vs 2mg klonopin - xanax high cholesterol

Anonymous said...

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

Anonymous said...

http://bayshorechryslerjeep.com/#8193 xanax 0.5mg side effects - where to buy xanax online forum

Anonymous said...

buy xanax online xanax bars oxycontin - 2mg of xanax high

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...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

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