oracle--------修改表空间
1、修改表的表空间
alter table TABLENAME move tablespace TABLESPACENAME;
2、修改索引的表空间
alter index INDEXNAME rebuild tablespace TABLESPACENAME;
3、修改有CLOB字段的表空间
alter table TABLENAME move tablespace TABLESPACENAME lob(COLUMNNAME) store as(tablespace TABLESPACENAME);
/*
----修改表
select 'alter table '||a.table_name||' move tablespace ERMIS_GUIZHOU;',
a.owner,a.table_name,a.tablespace_name,a.* from dba_tables a
where a.Owner='ERMIS_GUIZHOU' and a.TABLESPACE_NAME='LRMIS'
order by a.owner,a.table_name;
----修改索引
select 'alter index '||a.index_name||' rebuild tablespace ERMIS_GUIZHOU;',
a.owner,a.index_name,a.tablespace_name,a.* from dba_indexes a
where a.Owner='ERMIS_GUIZHOU' and a.TABLESPACE_NAME='LRMIS'
order by a.owner,a.table_name;
----修改clob索引
select 'alter table '||a.TABLE_NAME||' MOVE TABLESPACE ERMIS_GUIZHOU LOB ('||a.COLUMN_NAME||') STORE AS(TABLESPACE ERMIS_GUIZHOU);',
a.*
from user_lobs a
where a.TABLESPACE_NAME='LRMIS'
order by a.table_name;
*/