Friday, July 29, 2011

Moving Schema to another Tablespace using "move" command.


Purpose of Moving?

We move schema or tables to other tablespace in-order to remove fragmentation.

Let’s do this:

We here move the Scott schema which is currently present in users tablespace.

SQL>Create tablespace mytab datafile ‘D:\oracle\product\10.2.0\oradata\mytab.dbf’ size 5m;

Tablespace created

Now check the current tablespace of Scott:

SQL> select owner, tablespace_name from dba_tables where owner='SCOTT';

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
SCOTT                          USERS

Set spool on:

SQL> spool  D:\move.sql

SQL> select 'alter table ' || table_name || ' move tablespace mytab ;' from dba_
tables where owner='SCOTT' ;

'ALTERTABLE'||TABLE_NAME||'MOVETABLESPACEMYTAB;'
------------------------------------------------------------------
alter table DEPT move tablespace mytab ;
alter table EMP move tablespace mytab ;
alter table BONUS move tablespace mytab ;
alter table SALGRADE move tablespace mytab ;
alter table TEST_LOGMNR move tablespace mytab ;

SQL> spool off

A script is created as move.sql containing the list of tables to be move.

Now, run this script

SQL>@move.sql

The table move will change the ROWIDs of the table rows, and as such the indexes, which are based on ROWIDs, will become invalid (UNUSABLE).Therfeore, we need to rebuild them too.

An advantage of using the table move procedure is all constraints are preserved.

SQL>spool D:\index.sql

SQL> select 'Alter index ' || index_name || ' rebuild tablespace mytab ;' from dba_indexes where owner='SCOTT' ;

SQL>conn scott/tiger

SQL>@index.sql

Now check the tablespace of user scott

SQL> select owner, tablespace_name from dba_tables where owner='SCOTT';

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
SCOTT                          MYTAB

ENJOY:-)


12 comments:

Anonymous said...

Thank you in advance!!!

Anonymous said...

Usually I do not read article on blogs, however I wish to say that this write-up very forced me to take
a look at and do it! Your writing style has been surprised me.
Thanks, quite nice post.

my homepage ... Movers Dayton

Anonymous said...

Usually I do not read article on blogs, however I wish to say that this write-up very forced me to take a look at and do it!
Your writing style has been surprised me. Thanks, quite nice post.



Also visit my page Movers Dayton
My webpage - Ohio Moving

Anonymous said...

naturally like your website but you have to test the spelling on quite a few of your posts.
Many of them are rife with spelling problems and I find it
very troublesome to inform the reality on the other hand I'll certainly come back again.

Take a look at my webpage Ohio Moving

Anonymous said...

Have you ever thought about adding a little bit more than just your articles?
I mean, what you say is fundamental and all.
Nevertheless think of if you added some great photos or video
clips to give your posts more, "pop"! Your content is excellent but with images and clips, this blog could certainly be one of
the most beneficial in its field. Very good blog!



Here is my blog http://slimlipoplusdiet.com

Anonymous said...

For hottest news you have to go to see internet and on the web I found this web site as
a most excellent web site for hottest updates.

my website :: Natural Cleanse Diet

oakleyses said...

cheap oakley sunglasses, nike air max, jordan shoes, polo outlet, longchamp outlet, louis vuitton, ray ban sunglasses, tory burch outlet, longchamp pas cher, oakley sunglasses, replica watches, longchamp outlet, nike free run, louis vuitton outlet, nike air max, polo ralph lauren, christian louboutin outlet, nike outlet, oakley sunglasses, burberry pas cher, oakley sunglasses, tiffany jewelry, louis vuitton, prada outlet, replica watches, ray ban sunglasses, jordan pas cher, sac longchamp pas cher, chanel handbags, tiffany and co, ugg boots, oakley sunglasses wholesale, longchamp outlet, uggs on sale, polo ralph lauren outlet online, christian louboutin uk, michael kors pas cher, gucci handbags, christian louboutin, christian louboutin shoes, prada handbags, nike roshe, nike free, air max, kate spade outlet, ray ban sunglasses, ugg boots, louboutin pas cher

oakleyses said...

michael kors outlet, hollister pas cher, ralph lauren uk, michael kors, north face uk, coach outlet store online, true religion outlet, mulberry uk, abercrombie and fitch uk, guess pas cher, true religion outlet, lululemon canada, ray ban pas cher, michael kors, uggs outlet, vans pas cher, nike free uk, michael kors outlet, michael kors outlet online, replica handbags, nike tn, michael kors outlet, oakley pas cher, burberry outlet, polo lacoste, nike air max uk, nike blazer pas cher, hollister uk, michael kors outlet online, sac hermes, uggs outlet, michael kors outlet online, burberry handbags, new balance, coach purses, true religion jeans, north face, kate spade, ray ban uk, michael kors outlet online, true religion outlet, nike air max, converse pas cher, nike air force, nike air max uk, nike roshe run uk, sac vanessa bruno

oakleyses said...

ghd hair, hollister clothing, ray ban, iphone cases, celine handbags, oakley, insanity workout, converse, abercrombie and fitch, instyler, gucci, lululemon, ferragamo shoes, baseball bats, nike huaraches, beats by dre, babyliss, louboutin, new balance shoes, mac cosmetics, hollister, herve leger, hermes belt, giuseppe zanotti outlet, timberland boots, vans, valentino shoes, nike trainers uk, asics running shoes, p90x workout, nfl jerseys, nike air max, vans outlet, reebok outlet, soccer shoes, north face outlet, soccer jerseys, converse outlet, longchamp uk, nike roshe run, wedding dresses, ralph lauren, hollister, bottega veneta, north face outlet, mont blanc pens, nike air max, jimmy choo outlet, mcm handbags, chi flat iron

oakleyses said...

canada goose outlet, canada goose, toms shoes, links of london, karen millen uk, doudoune moncler, pandora jewelry, louis vuitton, marc jacobs, montre pas cher, canada goose outlet, moncler uk, moncler, pandora charms, juicy couture outlet, louis vuitton, thomas sabo, replica watches, canada goose jackets, ugg uk, ugg, canada goose, ugg,uggs,uggs canada, louis vuitton, wedding dresses, juicy couture outlet, canada goose, swarovski, moncler, canada goose outlet, supra shoes, moncler outlet, lancel, coach outlet, canada goose uk, louis vuitton, moncler outlet, moncler, louis vuitton, swarovski crystal, ugg pas cher, barbour, pandora uk, barbour uk, ugg,ugg australia,ugg italia, moncler, hollister, pandora jewelry

raybanoutlet001 said...

nike shoes
reebok shoes
cheap oakley sunglasses
oklahoma city thunder jerseys
coach outlet online
ugg outlet
nike blazer pas cher
ugg boots
coach outlet
gucci shoes

jeje said...

adidas yeezy boost
adidas tubular
harden shoes
jordan 12
moncler
jordan retro 13
adidas yeezy boost
hermes birkin
nike air max 2017
nike huarache