How is a tablespace taken offline and online?

Are you preparing for IT certification? With practice questions, study notes, interactive quizzes, tips and technical articles, uCertify PrepKits ensure that you get a solid grasp of core technical concepts to ace your certification exam in first attempt.

How is a tablespace taken offline and online?

Rating:

The ALTER TABLESPACE TablespaceName OFFLINE; statement is used to take a tablespace offline. When a tablespace is taken offline, all associated data files are also taken offline. Taking a database's tablespace offline makes the tablespace unavailable for general use, while the rest of the database is available for general use. When a tablespace is taken offline and again taken online, the event is recorded in the control file. A database's tablespace that is offline at the time the database is shut down remains offline when the database is re-started. A tablespace can be dropped while it is offline.

When taking a tablespace offline using the ALTER TABLESPACE...OFFLINE statement, the following options can be specified:

NORMAL: It specifies that a checkpoint is performed before taking a tablespace offline. It also ensures that a media recovery is not required when the tablespace is taken back online. When specifying NORMAL to take a tablespace offline, all data files of the tablespace must be online.

TEMPORARY: It takes a tablespace offline even if one or more data files of the tablespace are currently offline due to some error. It also performs a checkpoint before taking the tablespace offline. When taking a tablespace offline using the TEMPORARY option, if all data files are online, media recovery is not required when taking the tablespace back online. However, if any of the data files is offline while taking the tablespace offline, a media recovery is required when taking the tablespace back online.

IMMEDIATE: It specifies that a checkpoint is never performed before taking a tablespace offline, and that media recovery is always performed when taking the tablespace back online. A database's tablespace cannot be taken offline using the IMMEDIATE option if the database is running in NOARCHIVELOG mode.

FOR RECOVER: It takes a tablespace offline for point-in-time recovery of the tablespace. The associated data files can be copied from a backup, and archived log files can be applied to recover the tablespace. This option is deprecated in Oracle9i, and it is available only for backward compatibility.

Note: If an option is not specified, NORMAL is taken by default.

A tablespace is taken online using the ALTER TABLESPACE TablespaceName ONLINE; statement.

Rating: