[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]
转移CLOB字段
参考资料
[url]http://blog.sina.com.cn/s/blog_713bf4fe01011cyu.html[/url]
[url]http://xsb.itpub.net/post/419/38506[/url]
分区表信息: 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]