moving tables across tablespaces(zt from asktom)

Raghu -- Thanks for the question regarding "moving tables across tablespaces", version
originally submitted on 2-May-2000 13:20 Eastern US time, last updated 14-Apr-2005 18:04Tom's latest followup | GOTO a Bookmarkable Page | Bottom
You Asked (Jump to Tom's latest followup)

Tom, What is the best way to move tables from one
tablespace to another tablespace in the same instance?



and we said...


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

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


followup to comment #8

regarding the order columns.


No, it should not be like that. It is correct as presented:

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.

附:

如何迁移lob段的存储:

How do I move lob indexes and lob segments from one tablespace to another. 

and we said...

if you have a Table T and a lob column Y, it would be:

alter table t move lob(y) store as ( tablespace users )
/



....

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-796093/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-796093/