Oracle Tablespace之间数据迁移

[size=large]分区表常用的数据字典

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

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

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