創建表空間
CREATE SMALLFILE TABLESPACE "TABLESPACE "
DATAFILE 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\TABLESPACE _user\TABLESPACE '
SIZE 50M LOGGING EXTENT MANAGEMENT
LOCAL SEGMENT SPACE MANAGEMENT AUTO
更換表空間：
select 'alter table '||table_name|| ' move tablespace new_tablespace_name;' from all_tables where tablespace_name='old_tablespace_name';
更換索引表空間：
BLOB字段
select 'alter table ' || table_name ||
' MOVE tablespace tablespace _name LOB (' ||
(select column_name
from cols
where table_name = dba_indexes.table_name
and DATA_TYPE
like '%LOB' and rownum = 1 )|| ') STORE AS (TABLESPACE tablespace _name );' from
dba_indexes
where tablespace_name = 'old_tablespace _name '
and index_type = 'LOB'
說明：用了 rownum = 1 可能有些字段無法更新，需要查看如：
select *
from cols where table_name='' and data_type like '%LOB'
普通字段：
select 'alter index '||index_name||' rebuild tablespace tablespace _name;'
from dba_indexes
where tablespace_name=''
查詢在舊空間是否還存在索引
select * from dba_indexes where tablespace_name='' and table_owner=''