Oracle offline 表空间详解

Oracle offline 表空间详解

一、Offline的整体效果：offline以后，未完成的事物可以提交或回滚，但不能发起新的事物，也不能进行查询。

二、Offline的语法：

ALTER TABLESPACE tablespace
{ONLINE
|OFFLINE [NORMAL|TEMPORARY|IMMEDIATE|FOR RECOVER]}

三、将表空间offline的几个参数

（1）NORMAL ：

A tablespace can be taken offline normally if no error conditions exist for any of the datafiles of the tablespace. No datafile in the tablespace can be currently offline as the result of a write error. When OFFLINE NORMAL is specified, Oracle takes a checkpoint for all datafiles of the tablespace as it takes them offline. NORMAL is the default.

用法：Alter tablespace users offline;
Alter tablespace users online;

(2)TEMPORARY ：

A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. When OFFLINE TEMPORARY is specified, Oracle takes offline the datafiles that are not already offline, checkpointing them as it does so.

If no files are offline, but you use the temporary option, media recovery is not required to bring the tablespace back online. However, if one or more files of the tablespace are offline because of write errors, and you take the tablespace offline temporarily, the tablespace requires recovery before you can bring it back online.

用法：Alter tablespace users offline TEMPORARY;
Alter tablespace users online;

(3)IMMEDIATE ：

A tablespace can be taken offline immediately, without Oracle taking a checkpoint on any of the datafiles. When you specify OFFLINE IMMEDIATE, media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in NOARCHIVELOG mode.

用法：Alter tablespace users offline IMMEDIATE;
recover  tablespace users;
Alter tablespace users online;

(4)FOR RECOVER :

Takes the database tablespaces in the recovery set offline for tablespace point-in-time recovery. For additional information, see Oracle9i User-Managed Backup and Recovery Guide.

用法：Alter tablespace users offline FOR RECOVER;

recover  tablespace users;
Alter tablespace users online;

   
总结：

NORMAL ：做检查点

TEMPORARY：可以在数据文件损坏的情况下offline tablespace

IMMEDIATE:不做检查点，在归档模式下才可以offline，online时需要recover

FOR RECOVER：在归档模式下才可以offline，在基于时间点的恢复时使用

转自：http://blog.csdn.net/zero_plus/article/details/6146969

OCP试题

Your database is running in the ARCHIVELOG mode. You placed a tablespace, tbs_1, offline with the
immediate option. Which statement is correct in this scenario?
A. The operation would fail if tbs_1 were a read­only tablespace.
B. The operation would fail if tbs_1 were a read/write tablespace.
C. The operation would fail if tbs_1 were the default tablespace for the database.
D.  If  the  above  operation  were  successful,  media  recovery  would  be  required  to  bring  the  tablespace
online.
E. If the above operation were successful, instance recovery would be required to bring the tablespace
online.
Answer: D