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#
1687922              1687881

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#
  1688014       1688014    1687881

----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#
1688088              1688014

----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#
1688258       1688258    1688014

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#
1688302              1688014

----证明了文档中的如下说法：
----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#
1688302              1688014

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#
1688302       1688385    1688014

--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#
1688518              1688014