Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Moving tables into new tablespace

Re: Moving tables into new tablespace

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/07/01
Message-ID: <8jkv8m$49a$1@nnrp1.deja.com> 






In article <dw865.6056$LX4.120655_at_weber.videotron.net>,
  "Syltrem" <syltrem_at_videotron.ca> wrote:


> Hi!

>

> I want to move some tables from one tablespace to another and I don't

 know


> what's the best way to do it.

>

> I want to make sure my indexes and other constraints will follow.

>

> Also, is there a way to know how much % of the space used by an index

 is


> actually being used (equivalent to tables's dba_tables.empty_blocks)

>

> Can someone help?

>

> Thanx

>

>




I'll give you the Oracle8.0 and before answer as well as the
Oracle8i and above answer (in 8i, this is *trivial*, in 8.0 and
before, it takes practice and time)....




There are 2 methods we can use to do this.  One is to use a
combination of "alter table X move tablespace Y" and "alter
index X rebuild tablespace Y"  -- this works in Oracle8i release
8.1 and up ONLY.  Attached is a script called moveall.sql.



Moveall.sql


set echo off



column order_col1 noprint


column order_col2 noprint



set heading off


set verify off


set feedback off


set echo off



spool tmp.sql



select decode( segment_type, 'TABLE',

                       segment_name, table_name ) order_col1,
       decode( segment_type, 'TABLE', 1, 2 ) order_col2,
      'alter ' || segment_type || ' ' || segment_name ||
      decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
      chr(10) ||
      ' tablespace &1 ' || chr(10) ||
      ' storage ( initial ' || initial_extent || ' next ' ||
        next_extent || chr(10) ||
      ' minextents ' || min_extents || ' maxextents ' ||
        max_extents || chr(10) ||
      ' pctincrease ' || pct_increase || ' freelists ' ||
        freelists || ');'
  from user_segments,
       (select table_name, index_name from user_indexes )


 where segment_type in ( 'TABLE', 'INDEX' )
   and segment_name = index_name (+)


 order by 1, 2


/



spool off



set heading on


set verify on


set feedback on


set echo on



REM UNCOMMENT TO AUTO RUN the generated commands
REM ELSE edit tmp.sql, modify as needed and run it
REM @tmp






It uses the user_segments table to generate all of the needed
"alter table move" and "alter index rebuild" statements to move
a table/index into another tablespace preserving the storage
characteristics currently assigned to the object.  For example,
when we run moveall.sql in the SCOTT schema, we might see:



scott_at_ORACLE> @moveall users


scott_at_ORACLE> set echo off



alter TABLE ACCOUNT move


 tablespace users


 storage ( initial 10240 next 10240


 minextents 1 maxextents 121


 pctincrease 50 freelists 1);



alter TABLE BONUS move


 tablespace users


 storage ( initial 10240 next 10240


 minextents 1 maxextents 121


 pctincrease 50 freelists 1);



alter TABLE DEPT move


 tablespace users


 storage ( initial 10240 next 10240


 minextents 1 maxextents 121


 pctincrease 50 freelists 1);



alter INDEX PK_DEPT rebuild


 tablespace users


 storage ( initial 10240 next 10240


 minextents 1 maxextents 121


 pctincrease 50 freelists 1);



....



It begins by moving a table and then rebuilding each of the
indexes on that table.  Since the indexes on the tables being
moved will become unusable after the table, this script rebuilds
them right after moving a table -- before moving the next table
(to reduce downtime).



Running the moveall.sql script is harmless as it is written.  It
generates the SQL you need to run and saves the sql into yet
another script file "tmp.sql".  You should edit tmp.sql, review
it, modify it if you want (eg: if you have a multi-cpu system,
you could modify the index rebuilds to be "parallel N",
"unrecoverable" and add other options to make them go faster on
your system), and then run it.




Another method would be to use EXPort and IMPort.  You would



o EXP the user account.


o drop all of the objects this user owns.  You can 'select' the
drop statements you need (script attached) in much the same way
we 'select' the alter table/index statements
o revoke UNLIMITED TABLESPACE from the user
o alter the users default tablespace to the target tablespace
o give the user an unlimited quota on this new tablespace and
their temporary tablespace


o IMP this users data.



So, the process to move SCOTT's objects from their current
tablespace to a NEW_TABLESPACE would be:




    
  
  1.   do an export of all of scott's objects.  Make sure no one
modifies them after you begin this process.  You will lose these
changes if they do.






   $ exp userid=scott/tiger owner=scott



2) you would drop all of scotts tables.  This will get the
indexes as well.  I don't suggest dropping the user SCOTT but
rather dropping scott's objects.  Dropping scott would cause any
system priveleges SCOTT has to disappear and the import would
not restore them.  This script can be used to drop someones
tables:





set heading off


set feedback off


set verify off


set echo off



spool tmp.sql


select 'drop table &1..' || table_name || ' cascade
constraints;'


  from dba_tables


 where owner = upper('&1')


/


spool off


@tmp.sql







3) You would modify the user to *not* have unlimited tablespace
(else the IMP will just put the objects right back into the
tablespace they came from) and then give them unlimited quota's
on the new tablespace you want the objects to go into and on
their temporary tablespace (for the sorts the index creates will
do)



alter user SCOTT default tablespace NEW_TABLESPACE
/


revoke unlimited tablespace from SCOTT


/


alter user SCOTT quota unlimited on NEW_TABLESPACE
/


alter user SCOTT quota unlimited on SCOTTS_TEMPORARY_TABLESPACE
/



4) you will IMP the data back in for that user.  IMP will
rewrite the create statements to use the users default
tablespace when it discovers that it cannot create the objects
in their original tablespace.  Please make sure to review the
file imp.log after you do this for any and all errors after you
import.



imp userid=scott/tiger full=y ignore=y log=imp.log




5) you can optionally restore 'unlimited tablespace' to this
user (or not).  If you do not, this user can only create objects
in this new tablespace and temp (which in itself is not a bad
thing)...





As with any operation of this magnitude -- please test these
procedures on a small test account (such as SCOTT) to become
familar with them.




A couple of side notes:



o the alter table move/alter index rebuild is more flexible and
faster the exp/imp (and less error prone -- you never actually
drop the objects).  Additionally, it would be easy to modify the
script to move TABLES to one tablespace and INDEXES to a
different tablespace.  The drawback to using this method is the
you cannot move a table with a LONG or LONG RAW.  You must exp
that table and imp it into a table.  You can do this easily  by
exporting the table with the LONG/LONG RAW, dropping that table
-- creating an empty version of this table in the new tablespace
and importing just that table.



o if you use the exp/imp, it is upto you to ensure that no
modifications happen to the tables after you begin the export.
There are no mechanisms in place to ensure this -- you must do
this (else you will lose changes)






--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.


Received on Sat Jul 01 2000 - 00:00:00 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US