When to Use Composite Range-List Partitioning - Oracle 10g

You can take an online tablespace offline so that it is temporarily unavailable for general use. The rest of the database remains open and available for users to access data. Conversely, you can bring an offline tablespace online to make the schema objects within the tablespace available to database users. The database must be open to alter the availability of a tablespace.

To alter the availability of a tablespace, use the ALTER TABLESPACE statement. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.

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, the database takes all the associated files offline.

You cannot take the following tablespaces offline:

• SYSTEM
• The undo tablespace
• Temporary tablespaces

Before taking a tablespace offline, consider altering the tablespace allocation of any users who have been assigned the tablespace as a default tablespace. Doing so is advisable because those users will not be able to access objects in the tablespace while it is offline.

You can specify any of the following parameters as part of the ALTER TABLESPACE ... OFFLINE statement:

Clause table

Specify TEMPORARY only 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. Specify IMMEDIATE only after trying both the normal and temporary settings.

The following example takes the users tablespace offline normally:

Bringing Tablespaces Online

You can bring any tablespace in an Oracle Database online whenever the database is open. A tablespace is normally online so that the data contained within it is available to database users.

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

The following statement brings the users tablespace online: