TIP: The most easy way to move tables between schemas/tablespaces

From: bayard <master_at_unc.com.cn>
Date: 30 Dec 2001 00:49:27 -0800
Message-ID: <fe4c361a.0112300049.3100cf9e@posting.google.com>

Recently I have encounter such problems and search the net for best answers, now here is the summary of the way I choose, it may be helps to you.

1. Use exp utility to extract the tables you want to move. And exp will create a file call xxxx.dmp for you. Say the name of source tablespace is source_tblsp, the source user is source_usr.
2. Create target tablespace and schema you want to move to, say, target_tblsp and target_usr.
3. Create the same tablespace structure as the source on target instance for swap purpose. Say source_tblsp.
4. Grant target_usr the tablespace quota on source_tblsp on target instance.
5. Use imp utility to import tables into the source_tblsp, notice you MUST use target_usr to do so.
6. Run script 1 to create the move statement, you should provide 2 parameters to identify which target tablespace of tables and target tablespace of indexes, the script will create a tmp.sql for you.
7. Run the generated script as target_usr.
8. You tables will now be fullly moved to another tablespace/schema.

NOTICE:
The method described above works for LOBs, but partitions information seems to be lost. Will anyone help me to complete a full guideline for this topic.

Attachment script: (Originally by an oracle expert from oracle corp. thanks him!)

set echo off

column order_col1 noprint
column order_col2 noprint

set heading off
set verify off
set feedback off
set echo off

spool h:\temp\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) ||
      decode( segment_type, 'TABLE', ' tablespace &1 ', ' tablespace
&2 ' ) || 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 Received on Sun Dec 30 2001 - 02:49:27 CST