修改表空间 查询owner下 对于表级别的表空间分类 select tablespace_name,count(*) from dba_tables where owner = 'OPERATION_DEVELOP_CNEMC' group by tablespace_name order by 2; 核对上面的数量情况 select * from dba_tables where owner = 'OPERATION_DEVELOP_CNEMC' and tablespace_name = 'USERS'; 查看每张表的基本信息 select table_name,num_rows from dba_tables where owner = 'OPERATION_DEVELOP_CNEMC' and tablespace_name = 'USERS' order by 2 desc; 查询需要move表的详细信息 select t.table_name as "表名", t.num_rows as "表行数", nvl(s.partition_name, 0) as "分区名", s.segment_type "段类型", s.bytes / 1024 / 1024 as "段大小(MB)" FROM dba_tables t, dba_segments s where t.table_name = s.segment_name(+) and t.owner = 'OPERATION_DEVELOP_CNEMC' and t.tablespace_name = 'USERS' order by s.bytes desc; select count(*) from( select table_name from dba_tables where owner = 'OPERATION_DEVELOP_CNEMC' and table_name not like 'T\_%' escape '\' and table_name not like 'JF\_%' escape '\' and table_name not like 'PAYMENT\_%' escape '\' and table_name not like 'PROM%' and table_name <> 'CUSTOMER_BUY_HIS'); 查询需要move表的索引情况,11g中要考虑move后的索引重建,12c版本中可以支持online move select index_name,index_type,table_name,table_owner,table_type,uniqueness,status from dba_indexes where owner = 'OPERATION_DEVELOP_CNEMC' and tablespace_name = 'USERS'; 拼接move的sql语句 SELECT 'alter table '||TABLE_NAME||' move tablespace CNEMC;' FROM DBA_TABLES WHERE TABLESPACE_NAME = 'USERS' and OWNER = 'OPERATION_DEVELOP_CNEMC' order by TABLE_NAME; 拼接重建索引的sql语句(rebuild 跟 rebuid online的区分要注意,online不会阻塞dml语句)
select 'alter index ' ||index_name||' rebuild online tablespace CNEMC;' from dba_indexes where table_owner = 'OPERATION_DEVELOP_CNEMC' and status <> 'VALID'order by index_name
1、要考虑move后的索引重建问题
2、尽量放在业务低峰期或者夜间进行
3、对于大表考虑重建索引时占用的cpu跟临时表空间的问题
vinson