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
--- eof ----
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@ORACLE> @moveall
scott@ORACLE> set echo off
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 itselfis 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)
How the above code works:
select decode( segment_type, 'TABLE',
segment_name,
table_name ) order_col1,
decode( segment_type,
'TABLE', 1, 2 ) order_col2,
If the segment_type is a table, return the SEGMENT_NAME
(which is actually the table
name). Else, if the segment type is not a table (it is
an index) return the TABLE_NAME
that the index is on. This'll group a table and all of
its indexes together.
The second column just makes sure that tables are done PRIOR
to their indexes.
