You can alter the availability of individual datafiles or tempfiles by taking them offline or bringing them online. Offline datafiles are unavailable to the database and cannot be accessed until they are brought back online.
Reasons for altering datafile availability include the following:
You want to perform an offline backup of a datafile.
You want to rename or relocate a datafile. You must first take it offline or take the tablespace offline.
A datafile becomes missing or corrupted. You must take it offline before you can open the database.
The datafiles of a read-only tablespace can be taken offline or brought online, but bringing a file online does not affect the read-only status of the tablespace. You cannot write to the datafile until the tablespace is returned to the read/write state.
Note:You can make all datafiles of a tablespace temporarily unavailable by taking the tablespace itself offline. You must leave these files in the tablespace to bring the tablespace back online, although you can relocate or rename them following procedures similar to those shown in "Renaming and Relocating Datafiles".
For more information, see "Taking Tablespaces Offline".
To take a datafile offline or bring it online, you must have the
ALTER DATABASE system privilege. To take all datafiles or tempfiles offline using the
ALTER TABLESPACE statement, you must have the
ALTER TABLESPACE or
MANAGE TABLESPACE system privilege. In an Oracle Real Application Clusters environment, the database must be open in exclusive mode.
This section describes ways to alter datafile availability, and contains the following topics:
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;
To take the same file offline, issue the following statement:
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;
Note:To use this form of the
ALTER DATABASEstatement, the database must be in
ARCHIVELOGmode. This requirement prevents you from accidentally losing the datafile, since taking the datafile offline while in
NOARCHIVELOGmode is likely to result in losing the file.
OFFLINE keyword causes the database to mark the datafile
OFFLINE, whether or not it is corrupted, so that you can open the database.
DROP keywords mark the datafile for subsequent dropping. Such a datafile can no longer be brought back online.
Note:This operation does not actually drop the datafile. It remains in the data dictionary, and you must drop it yourself using one of the following methods:
DROP, this method works for dictionary managed tablespaces only.
If the preceding methods fail, an operating system command to delete the datafile. This is the least desirable method, as it leaves references to the datafile in the data dictionary and control files.
The following statement takes the specified datafile offline and marks it to be dropped:
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;
Clauses of the
ALTER TABLESPACE statement allow you to change the online or offline status of all of the datafiles or tempfiles within a tablespace. Specifically, the statements that affect online/offline status are:
You are required only to enter the tablespace name, not the individual datafiles or tempfiles. All of the datafiles or tempfiles are affected, but the online/offline status of the tablespace itself is not changed.
In most cases the preceding
ALTER TABLESPACE statements can be issued whenever the database is mounted, even if it is not open. However, the database must not be open if the tablespace is the
SYSTEM tablespace, an undo tablespace, or the default temporary tablespace. The
ALTER DATABASE DATAFILE and
ALTER DATABASE TEMPFILE statements also have
ONLINE/OFFLINE clauses, however in those statements you must enter all of the filenames for the tablespace.
The syntax is different from the
ALTER TABLESPACE...ONLINE|OFFLINE statement that alters tablespace availability, because that is a different operation. The
ALTER TABLESPACE statement takes datafiles offline as well as the tablespace, but it cannot be used to alter the status of a temporary tablespace or its tempfile(s).