Oracle7 Server Administrator's Guide

Altering Tablespace Availability

You can bring an offline tablespace online to make the schema objects within the tablespace available to database users. Alternatively, you can take an online tablespace offline while the database is open, so that this portion of the database is temporarily unavailable for general use but the rest is open and available. This section includes the following topics:

Bringing Tablespaces Online

Taking Tablespaces Offline

Bringing Tablespaces Online

You can bring any tablespace in an Oracle database online whenever the database is open. The only exception is that the SYSTEM tablespace must always be online because the data dictionary must always be available to Oracle. A tablespace is normally online so that the data contained within it is available to database users.

To bring an offline tablespace online while the database is open, use either the Place Online menu item of Server Manager/GUI, or the SQL command ALTER TABLESPACE. You must have the MANAGE TABLESPACE system privilege to bring a tablespace online.

Note: If a tablespace to be brought online was not taken offline "cleanly" (that is, using the NORMAL option of the ALTER TABLESPACE OFFLINE command), you must first perform media recovery on the tablespace before bringing it online. Otherwise, Oracle7 returns an error and the tablespace remains offline.

The following statement brings the USERS tablespace online:

ALTER TABLESPACE users ONLINE;

Taking Tablespaces Offline

You may wish 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.

To take an online tablespace offline while the database is open, use either the Take Offline menu item of Server Manager/GUI, or the SQL command ALTER TABLESPACE. You must have the MANAGE TABLESPACE system privilege to take a tablespace offline.

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

normal offline 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. With normal offline priority, Oracle7 takes a checkpoint for all datafiles of the tablespace as it takes them offline.
temporary offline A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. With temporary offline priority, Oracle7 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 will require recovery before you can bring it back online.
immediate offline A tablespace can be taken offline immediately, without Oracle's taking a checkpoint on any of the datafiles. With immediate offline priority, 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.

Warning: If you must take a tablespace offline, use the normal option (the default) if possible; this guarantees that the tablespace will not require recovery to come back online, even if you reset the redo log sequence (using an ALTER DATABASE OPEN RESETLOGS statement after incomplete media recovery) before bringing the tablespace back online.

Take a tablespace offline temporarily only when you cannot take it offline normally; in this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online. Take a tablespace offline immediately only after trying both the normal and temporary options.

The following example takes the USERS tablespace offline normally:

ALTER TABLESPACE users OFFLINE NORMAL;

normal offline	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. With normal offline priority, Oracle7 takes a checkpoint for all datafiles of the tablespace as it takes them offline.
temporary offline	A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. With temporary offline priority, Oracle7 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 will require recovery before you can bring it back online.
immediate offline	A tablespace can be taken offline immediately, without Oracle's taking a checkpoint on any of the datafiles. With immediate offline priority, 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.