tablespace offline的几种方式比较

tablespace offline的4种方式
-normal
   对属于该表空间的所有数据文件做检查点并且offline，说明两点：
   1.表空间所有文件不能有错
   2.表空间所有文件都要online
-temporary
   对属于表空间且非offline的文件做检查点并且offline，online的时候要对原先已经offline的文件做  recover
-immediate
   都不做检查点
   非归档不能用
-for recover
   10g已经取消
实验一：表空间有一个文件offline，则不能采用offline normal来脱机表空间
sys@FOX> alter database datafile 9 offline drop;

Database altered.

sys@FOX> alter tablespace tools offline;
alter tablespace tools offline
*
ERROR at line 1:
ORA-01191: file 9 is already offline - cannot do a normal offline
ORA-01110: data file 9: 'D:\TOOL02.DBF'

实验二：表空间有一个文件错误，则不能采用offline normal来脱机表空间
sys@FOX> alter tablespace tools offline;
alter tablespace tools offline
*
ERROR at line 1:
ORA-01122: database file 9 failed verification check
ORA-01110: data file 9: 'D:\TOOL02.DBF'
ORA-01251: Unknown File Header Version read for file number 9

实验三：表空间有一个文件损坏，但是还是online状态，不能做offline temporary
做完全检查点后，oracle会将其置为offline状态，这时就可以使用temporary选项了
sys@FOX> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

FILE# STATUS  CHECKPOINT_CHANGE# NAME
----- ------- ------------------ ---------------------------------------------
    1 SYSTEM          2460064082 D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF
    2 ONLINE          2460064082 D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF
    3 ONLINE          2460064082 D:\ORACLE\ORADATA\FOX\INDX01.DBF
    4 ONLINE          2460064082 D:\ORACLE\ORADATA\FOX\TOOLS01.DBF
    5 ONLINE          2460064082 D:\ORACLE\ORADATA\FOX\USERS01.DBF
    6 ONLINE          2460064082 D:\ORACLE\ORADATA\FOX\DEMO01.DBF
    7 ONLINE          2460064082 D:\BACKUP_DIR\TESTING_LMT.DBF
    8 ONLINE          2460064082 D:\ORACLE\ORADATA\FOX\RMAN.DBF
    9 ONLINE          2460064082 D:\TOOL02.DBF

9 rows selected.

sys@FOX> select FILE#,checkpoint_change#,recover, fuzzy from v$datafile_header;

FILE# CHECKPOINT_CHANGE# REC FUZ
----- ------------------ --- ---
    1         2460064082 NO  YES
    2         2460064082 NO  YES
    3         2460064082 NO  YES
    4         2460064082 NO  YES
    5         2460064082 NO  YES
    6         2460064082 NO  YES
    7         2460064082 NO  YES
    8         2460064082 NO  YES
    9         2460064082     YES

9 rows selected.

sys@FOX> alter tablespace tools offline;
alter tablespace tools offline
*
ERROR at line 1:
ORA-01122: database file 9 failed verification check
ORA-01110: data file 9: 'D:\TOOL02.DBF'
ORA-01251: Unknown File Header Version read for file number 9


sys@FOX> alter tablespace tools offline temporary;
alter tablespace tools offline temporary
*
ERROR at line 1:
ORA-01122: database file 9 failed verification check
ORA-01110: data file 9: 'D:\TOOL02.DBF'
ORA-01251: Unknown File Header Version read for file number 9


sys@FOX> alter system checkpoint;

System altered.

sys@FOX> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

FILE# STATUS  CHECKPOINT_CHANGE# NAME
----- ------- ------------------ ---------------------------------------------
    1 SYSTEM          2460064551 D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF
    2 ONLINE          2460064551 D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF
    3 ONLINE          2460064551 D:\ORACLE\ORADATA\FOX\INDX01.DBF
    4 ONLINE          2460064551 D:\ORACLE\ORADATA\FOX\TOOLS01.DBF
    5 ONLINE          2460064551 D:\ORACLE\ORADATA\FOX\USERS01.DBF
    6 ONLINE          2460064551 D:\ORACLE\ORADATA\FOX\DEMO01.DBF
    7 ONLINE          2460064551 D:\BACKUP_DIR\TESTING_LMT.DBF
    8 ONLINE          2460064551 D:\ORACLE\ORADATA\FOX\RMAN.DBF
    9 RECOVER         2460064082 D:\TOOL02.DBF

9 rows selected.

sys@FOX> select FILE#,checkpoint_change#,recover, fuzzy,status from v$datafile_header;

FILE# CHECKPOINT_CHANGE# REC FUZ STATUS
----- ------------------ --- --- -------
    1         2460064551 NO  YES ONLINE
    2         2460064551 NO  YES ONLINE
    3         2460064551 NO  YES ONLINE
    4         2460064551 NO  YES ONLINE
    5         2460064551 NO  YES ONLINE
    6         2460064551 NO  YES ONLINE
    7         2460064551 NO  YES ONLINE
    8         2460064551 NO  YES ONLINE
    9         2460064082     YES OFFLINE

9 rows selected.

sys@FOX> alter tablespace tools offline temporary;

Tablespace altered.

理论参考官方文档
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#sthref1215