Re: How to move tables from one tablespace to another (Oracle 7.3.4)

From: Michael Sun <mikeny31_at_speakeasy.org>
Date: Mon, 1 May 2000 01:24:51 -0400
Message-Id: <10483.104529@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0132_01BFB30C.0C1A68A0 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I have posted this one before, but why not one more time. I can't claim = this script, it is from the very Mr. Thomas Kyte.

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)....
=20

you don't move procedures -- they are compiled into system and will = remain there forever. you can move tables and indexes mostly (there are = other things but this
covers most of it)...
=20

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. 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:
=20

scott_at_ORACLE> @moveall
scott_at_ORACLE> set echo off
=20

alter TABLE ACCOUNT move
tablespace users
storage ( initial 10240 next 10240
minextents 1 maxextents 121
pctincrease 50 freelists 1);
=20

alter TABLE BONUS move
tablespace users
storage ( initial 10240 next 10240
minextents 1 maxextents 121
pctincrease 50 freelists 1);
=20

alter TABLE DEPT move
tablespace users
storage ( initial 10240 next 10240
minextents 1 maxextents 121
pctincrease 50 freelists 1);
=20

alter INDEX PK_DEPT rebuild
tablespace users
storage ( initial 10240 next 10240
minextents 1 maxextents 121
pctincrease 50 freelists 1);
=20

....
=20

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).
=20

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

moveall.sql ------------------------------------ = set echo off
=20
column order_col1 noprint column order_col2 noprint
=20
set heading off set verify off set feedback off set echo off
=20
spool tmp.sql
=20
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)=20 || ' 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 =3D index_name (+) order by 1, 2 /
=20
spool off
=20
set heading on set verify on set feedback on set echo on ------------------------------------------------------------------=20
=20
Another method would be to use EXPort and IMPort. You would
=20
o EXP the user account. o drop all of the objects this user owns. You can 'select' the drop = statements you need 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.

------=_NextPart_000_0132_01BFB30C.0C1A68A0 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =

http-equiv=3DContent-Type>

<META content=3D"MSHTML 5.00.2919.6307" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>I have posted this one before, but why =

not one more=20
time. I can't claim this script, it is from the very Mr. Thomas=20 Kyte.</DIV>
<DIV> </DIV>
<DIV>I'll give you the Oracle8.0 and = before=20
answer as well as the Oracle8i and above answer (in 8i, this is = *trivial*, in=20
8.0 and before, it takes practice and time)....    you = don't=20
move procedures -- they are compiled into system and will remain there=20 forever.  you can move tables and indexes mostly (there are other = things=20
but this  covers most of it)...    There are 2 = methods=20
we can use to do this.  One is to use a combination of "alter table = X move=20
tablespace Y" and "alter index X rebuild tablespace Y"  -- this = works=20
in  Oracle8i release 8.1 and up ONLY.  Attached is a script = called=20
moveall.sql.  It uses the user_segments table to generate all of = the needed=20
"alter table move" and "alter  index rebuild" statements to move = a=20
table/index into another tablespace preserving the storage = characteristics=20
currently assigned to the object.  For example, when = we  run=20
moveall.sql in the SCOTT schema, we might see:  =20  scott_at_ORACLE> @moveall  scott_at_ORACLE> set echo=20 off    alter TABLE ACCOUNT move  tablespace=20 users  storage ( initial 10240 next 10240  minextents 1=20 maxextents 121  pctincrease 50 freelists 1);   =  alter=20
TABLE BONUS move  tablespace users  storage ( initial = 10240 next=20
10240  minextents 1 maxextents 121  pctincrease 50 = freelists=20
1);    alter TABLE DEPT move  tablespace=20 users  storage ( initial 10240 next 10240  minextents 1=20 maxextents 121  pctincrease 50 freelists 1);   =  alter=20
INDEX PK_DEPT rebuild  tablespace users  storage ( = initial 10240=20
next 10240  minextents 1 maxextents 121  pctincrease 50=20 freelists 1);    ....    It begins by = moving a=20
table and then rebuilding each of the indexes on that table.  Since = the=20
indexes on the tables being moved will become unusable after the table,=20 this  script rebuilds them right after moving a table -- before = moving=20
the next table (to reduce downtime).    Running the = moveall.sql=20
script is harmless as it is written.  It generates the SQL you need = to run=20
and saves the sql into yet another script file "tmp.sql". You should=20 edit  tmp.sql, review it, modify it if you want (eg: if you have = a=20
multi-cpu system, you could modify the index rebuilds to be "parallel = N",=20
"unrecoverable" and add other options  to make them go faster on = your=20

system), and then run it.<BR>&nbsp; <BR>&nbsp;--------------------- =

moveall.sql=20

------------------------------------ set echo off<BR>&nbsp; =

column=20
order_col1 noprint  column order_col2 noprint   =  set=20
heading off  set verify off  set feedback = off  set echo=20
off    spool tmp.sql    select decode( =

segment_type, 'TABLE', segment_name, table_name )=20 order_col1,       decode( segment_type, = 'TABLE',=20
1, 2 ) order_col2,        'alter ' ||=20 segment_type || ' ' || segment_name =
||       =20 decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) || chr(10)=20 ||       ' tablespace &1 ' || chr(10)=20 ||        ' storage ( initial ' ||=20 initial_extent || ' next ' || next_extent || chr(10)=20 ||        ' minextents ' || min_extents = || '=20
maxextents ' || max_extents || chr(10)=20  ||        ' pctincrease ' ||=20 pct_increase || ' freelists ' || freelists || ');'   from=20 user_segments, (select table_name, index_name from user_indexes ) where=20 segment_type in (  'TABLE', 'INDEX' )    and = segment_name =3D=20
index_name (+)  order by 1, 2  /   =  spool=20
off    set heading on  set verify = on  set=20
feedback on  set echo=20

on<BR>&nbsp;-------------------------------------------------------------=

-----=20
    Another method would be to use EXPort and = IMPort.  You=20
would    o EXP the user account.  o drop all of = the=20
objects this user owns.  You can 'select' the drop statements you = need in=20
much the same way we 'select' the alter table/index statements o=20 revoke  UNLIMITED TABLESPACE from the user  o alter the = users=20
default tablespace to the target tablespace  o give the user an=20 Received on Mon May 01 2000 - 00:24:51 CDT