Database Reference
In-Depth Information
Table 2-4.
Options for Taking Tablespaces/Data Files Offline
Statement
Archivelog Mode
Required?
Media Recovery Required When
Toggling Online?
Works in Mount
Mode?
ALTER TABLESPACE ... OFFLINE
NORMAL
No
No
No
ALTER TABLESPACE ... OFFLINE
TEMPORARY
No
Maybe: Depends on whether any
data files already have offline
status
No
ALTER TABLESPACE ... OFFLINE
IMMEDIATE
No
Yes
No
ALTER DATABASE DATAFILE ...
OFFLINE
Yes
Yes
Yes
ALTER DATABASE DATAFILE ...
OFFLINE FOR DROP
No
Yes
Yes
■
While the database is in mount mode (and not open), you can use the
ALTER DATABASE DATAFILE
command to
take any data file offline, including
SYSTEM
and
UNDO
.
Note
Renaming or Relocating a Data File
You may occasionally need to move or rename a data file. For example, you may need to move data files because of
changes in the storage devices or because the files were created in the wrong location or with a nonstandard name. As
of Oracle 12c, you have the option of renaming or moving data files, or both, while they are online. Otherwise, you will
have to take data files offline for maintenance operations.
Performing Online Data File Operations
Starting with Oracle 12c is the
ALTER DATABASE MOVE DATAFILE
command. This command allows you to rename
or move data files without any downtime. This vastly simplifies the task of moving or renaming a data file, as there
is no need to manually place data files offline/online and use OS commands to physically move the files. This once
manually intensive (and error-prone) operation has now been simplified to a single SQL command.
A data file must be online for the online move or rename to work. Here is an example of renaming an online data file:
SQL> alter database move datafile '/u01/dbfile/O12C/users01.dbf' to
'/u01/dbfile/O12C/users_dev01.dbf';
Here is an example of moving a data file to a new mount point:
SQL> alter database move datafile '/u01/dbfile/O12C/system01.dbf' to
'/u02/dbfile/O12C/system01.dbf';
You can also specify the data file number when renaming or moving a data file; for example,
SQL> alter database move datafile 2 to '/u02/dbfile/O12C/sysuax01.dbf';
