Oracle 11g 修改表的所属表空间

应领导要求，需要将部分表的所属表空间进行更改，目的为了整理用户。

查询owner下 对于表级别的表空间分类

select tablespace_name,count(*) from dba_tables where owner = 'XXX' group by tablespace_name order by 2;

核对上面的数量情况

select * from dba_tables where owner = 'XXX' and tablespace_name = 'USERS';

查看每张表的基本信息

select table_name,num_rows from dba_tables where owner = 'XXX' 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 = 'XXX'

and t.tablespace_name = 'USERS'

order by s.bytes

desc;

select count(*) from(

select table_name

from dba_tables

where owner = 'XXX'

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 = 'XXX' and tablespace_name = 'USERS';

拼接move的sql语句

SELECT 'alter table '||TABLE_NAME||' move tablespace YLPW;' FROM DBA_TABLES WHERE TABLESPACE_NAME = 'USERS' and OWNER = 'XXX';

拼接重建索引的sql语句(rebuild 跟 rebuid online的区分要注意，online不会阻塞dml语句)

select 'alter index ' ||index_name||' rebuild online;' from dba_indexes where table_owner = 'YL2012' and status <> 'VALID';

1、要考虑move后的索引重建问题

2、尽量放在业务低峰期或者夜间进行

3、对于大表考虑重建索引时占用的cpu跟临时表空间的问题