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.
See Also:"Altering Datafile Availability" for information about altering the availability of individual datafiles within a tablespace
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 rename or relocate tablespace datafiles
See "Renaming and Relocating Datafiles" for details.
When a tablespace is taken offline, the database takes all the associated files offline.
You cannot take the following tablespaces offline:
The undo tablespace
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.
||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 you specify
||A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. When you specify
If no files are offline, but you use the temporary clause, 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.
||A tablespace can be taken offline immediately, without the database taking a checkpoint on any of the datafiles. When you specify
Caution:If you must take a tablespace offline, use the
NORMALclause (the default) if possible. This setting guarantees that the tablespace will not require recovery to come back online, even if after incomplete recovery you reset the redo log sequence using an
ALTER DATABASE OPEN RESETLOGSstatement.
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:
ALTER TABLESPACE users OFFLINE NORMAL;
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.
See Also:Oracle Database Backup and Recovery User's Guide for information about performing media recovery
ALTER TABLESPACE users ONLINE;