从USERS表空间分离表数据和索引

前段时间某公司给做了个网站，装了个11g的数据库，安装后只有3个单独的日志文件，归档、闪回没有打开，网站的表数据和索引全建到USERS表空间，这种设计方法完全是错误的，因此需要对数据库进行整改。
   具体为日志文件增加到4组8个文件，分别放在不同的地方，归档、闪回打开；新建3个用户的表空间，分别用来放数据文件、索引文件、临时文件，整改方法见下：
1. 查日志文件位置：
select * from v$log;
select * from v$logfile;
--增加日志
ALTER DATABASE ADD LOGFILE MEMBER
     '/home/oracle/app/fast_recovery_area/eytdbs/REDO04.LOG' TO GROUP 1,
     '/home/oracle/app/fast_recovery_area/eytdbs/REDO05.LOG' TO GROUP 2,
     '/home/oracle/app/fast_recovery_area/eytdbs/REDO06.LOG' TO GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 4
    ('/home/oracle/app/oradata/eytdbs/REDO07.LOG',
     '/home/oracle/app/fast_recovery_area/eytdbs/REDO08.LOG') SIZE 50M reuse;
alter system switch logfile; --多切换几次日志
2. 打开归档：
SQL> shutdown immediate;        --关闭数据库
SQL> startup mount;      --以mount方式打开
SQL> alter database archivelog;      --修改运行模式
SQL> alter database open;            --打开数据库
SQL> archive log start;              --启用、停用自动存档
SQL> alter system switch logfile;         --强制日志切换进行归档
SQL> archive log list;        --再查询数据库运行模式

3. 打开闪回：
SQL> shutdown immediate; --与上面的归档一起做
SQL> startup mount;
SQL> alter database flashback on;
SQL> alter database open;
4. 建表空间：
--建sns的数据、索引、临时表空间：
CREATE TABLESPACE snsdata
   DATAFILE
     '/home/oracle/app/oradata/sns/snsdata.dbf' SIZE 400M REUSE AUTOEXTEND OFF
LOGGING
   ONLINE
   PERMANENT
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
CREATE TABLESPACE snsdindex
   DATAFILE
     '/home/oracle/app/oradata/sns/snsindex.dbf' SIZE 400M REUSE AUTOEXTEND OFF
LOGGING
   ONLINE
   PERMANENT
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
--修改表空间名：
alter tablespace SNSDINDEX rename to SNSINDEX;

--临时表空间
    CREATE TEMPORARY TABLESPACE snstemp
TEMPFILE
     '/home/oracle/app/oradata/sns/snstemp.dbf' SIZE 400M REUSE AUTOEXTEND OFF
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
--修改用户的默认、临时表空间：
alter user sns default tablespace snsdata;
alter user sns TEMPORARY TABLESPACE snstemp;
5. 分离数据：
--查当前用户下的所有表：
select 'alter table SNS.' || table_name || ' move tablespace snsdata' ||';' from dba_tables where owner='SNS';
--查当前用户的索引：
SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD TABLESPACE SNSINDEX; '
   FROM USER_INDEXES
WHERE TABLESPACE_NAME = 'USERS';
把上面查询出来的结果粘到command下面执行，即可。
用下面的语句验证下结果看是否分离完毕：
select TABLE_NAME,TABLESPACE_NAME from user_tables where TABLESPACE_NAME ='SNSDATA'
--以上仅供参考。

作者“srsunbing”