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:-)


9 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

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

5689 said...

zzzzz2018.8.31
off-white clothing
nike air max 95 ultra
reebok
ugg boots
giuseppe zanotti
nike huarache
football pas cher
basket nike
fitflops sale clearance
coach outlet store online

yanmaneee said...

lebron james shoes
supreme outlet
jordan shoes
lebron 10
balenciaga
supreme clothing
michael kors outlet online
yeezy supply
adidas nmd r1
adidas yeezy