将tablespace offline的几种方法

    最近生产库里有个表空间需要offline，为了慎重起见，查看了一下oracle 的document，如下：

Taking Tablespaces Offline

You may want to take a tablespace offline for any of the following reasons:

• To make a portion of the database unavailable while allowing normal access to the remainder of the database
• To perform. an offline tablespace backup (even though a tablespace can be backed up while online and in use)
• To make an application and its group of tables temporarily unavailable while updating or maintaining the application

When a tablespace is taken offline, Oracle takes all the associated files offline. TheSYSTEMtablespace can never be taken offline.

You can specify any of the following options when taking a tablespace offline:

Caution:

If you must take a tablespace offline, use theNORMALoption (the default) if possible. This guarantees that the tablespace will not require recovery to come back online. It will not require recovery, even if after incomplete recovery you reset the redo log sequence using anALTER DATABASE OPEN RESETLOGSstatement.

SpecifyTEMPORARYonly when you cannot take the tablespace offline normally. In this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online. SpecifyIMMEDIATEonly after trying both the normal and temporary options.

The following example takes theuserstablespace offline normally:

ALTER TABLESPACE users OFFLINE NORMAL;

Before taking an online tablespace offline, consider taking the following actions:

• Verify that the tablespace contains no active rollback segments. Such a tablespace cannot be taken offline.
• You may want to alter the tablespace allocation of any users who have been assigned the tablespace as either a default or temporary tablespace. This is advisable because they will not be able to access objects or sort areas in the tablespace while it is offline.

    可以看出，normal是首选方式，这种情况下oracle发出一个checkpoint，当将表空间online时不需要recover，如果normal方式不行，则选用TEMPORARY方式 ,使用此方式offline后,在online时只需recover有error的数据文件。最后一种方式就是immediate方式,此种方式不发出checkpoint,online时需要recover.