Oracle Tablespace之间数据迁移

分区表常用的数据字典

分区表信息:  dba_part_tables 
显示分区:    dba_tab_partitions 
显示子分区:  dba_tab_subpartitions 
显示分区列:  dba_part_key_columns 
显示子分区列:dba_subpart_dey_columns 
显示分区索引:dba_part_indexes 
显示索引分区:dba_ind_partitions

select 'alter table '||table_name||' move tablespace wzrq ;'
from user_all_tables where tablespace_name='GZNS' 
UNION ALL
select 'alter table '||table_name||' move tablespace wzrq ;'
from dba_lobs where tablespace_name='GZNS' 
union all
--分区表
select 'ALTER TABLE '|| table_name ||' MOVE PARTITION '||partition_name||' TABLESPACE WZRQ NOLOGGING; '
from  dba_tab_partitions  where 1=1 
and tablespace_name='GZNS'
order by table_name ,partition_position



--分区索引
select 'alter index '||index_name|| '  rebuild tablespace wzrq;' 
from user_indexes where index_type='NORMAL' and TABLESPACE_NAME='GZNS'
and dropped='NO'
union all
select 'alter index '||index_name|| '  rebuild tablespace wzrq;' 
from user_indexes where index_type='NORMAL' and table_owner='WZRQ'
and dropped='NO'

转移CLOB字段
 

 alter table KNOWLEDGEITEM move lob(KILOCALTEXT)  store as (tablespace  WZRQ); 
 alter table MAIL_JOB move lob(MAIL_CONTENT)  store as (tablespace  WZRQ);   
 alter table FAX_JOB move lob(FAX_CONTENT)  store as (tablespace  WZRQ);

参考资料
http://blog.sina.com.cn/s/blog_713bf4fe01011cyu.html
http://xsb.itpub.net/post/419/38506