offline tablespace的几种方法

---------------------------
方式1：offline normal:
---------------------------

select name 文件名,ts# 空间编号 from v$datafile;

文件名空间编号
E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF 0
E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF 1
E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF 2
E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF 4
E:\TEST.DBF 7

SQL>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=7;

CHECKPOINT_CHANGE#LAST_CHANGE#OFFLINE_CHANGE#
16879221687881

SQL> alter tablespace test offline normal;

SQL>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=7;

CHECKPOINT_CHANGE#LAST_CHANGE#OFFLINE_CHANGE#
168801416880141687881

----offline normal，tablespace内所有的数据文件上触发checkpoint。 checkpoint_change#增加

SQL> alter tablespace test online;

SQL>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=7;
CHECKPOINT_CHANGE#LAST_CHANGE#OFFLINE_CHANGE#
16880881688014

----online时，不需要media recovery，同时tablespace内所有的数据文件上再次触发checkpoint。 checkpoint_change#增加

---------------------------
方式2：offline temporary
---------------------------

SQL>alter tablespace test offline temporary;

SQL>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=7;
CHECKPOINT_CHANGE#LAST_CHANGE#OFFLINE_CHANGE#
168825816882581688014

SQL> alter tablespace test online;

表空间已更改。

SQL>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=7;
CHECKPOINT_CHANGE#LAST_CHANGE#OFFLINE_CHANGE#
16883021688014

----证明了文档中的如下说法：
----If no files are offline, but you use the temporary option, media recovery is not required to bring the tablespace back online

---------------------------
方式3：offline immediate
---------------------------
SQL>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=7;
CHECKPOINT_CHANGE#LAST_CHANGE#OFFLINE_CHANGE#
16883021688014

SQL> alter tablespace test offline immediate;

SQL>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=7;

CHECKPOINT_CHANGE#LAST_CHANGE#OFFLINE_CHANGE#
168830216883851688014

--offline immediate，tablespace内所有的数据文件上不触发checkpoint。 checkpoint_change#不变。

SQL> alter tablespace test online;
alter tablespace test online
*
第 1 行出现错误:
ORA-01113: 文件 5 需要介质恢复
ORA-01110: 数据文件 5: 'E:\TEST.DBF'

----将tablespace online 时需要media recovery

SQL> recover datafile 5;
完成介质恢复。

SQL> alter tablespace test online;

表空间已更改。

SQL>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=7;

CHECKPOINT_CHANGE#LAST_CHANGE#OFFLINE_CHANGE#
16885181688014