分区表常用的数据字典
分区表信息: 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