Files in Support of Backup and Recovery Operations - Oracle RMAN for Absolute Beginners - page 51

Then, use the TEMPFILE option of the ALTER DATABASE statement:

SQL> alter database tempfile '/u01/dbfile/O12C/temp01.dbf' resize 500m;

You can also add a file to a temporary tablespace via the ALTER TABLESPACE statement:

SQL> alter tablespace temp add tempfile '/u01/dbfile/O12C/temp02.dbf' size 5000m;

Toggling Data Files Offline and Online
Sometimes, when you're performing maintenance operations (such as renaming data files), you may need to first take
a data file offline. You can use either the ALTER TABLESPACE or the ALTER DATABASE DATAFILE statement to toggle data
files offline and online.

■ as of oracle 12c, you can move and rename data files while they are online and open for use. see “renaming or
relocating a data File,” later in this chapter, for a discussion of this.

Tip

Use the ALTER TABLESPACE ... OFFLINE NORMAL statement to take a tablespace and its associated data files
offline. You don't need to specify NORMAL , because it's the default:

SQL> alter tablespace users offline;

When you place a tablespace offline in normal mode, Oracle performs a checkpoint on the data files associated
with the tablespace. This ensures that all modified blocks in memory that are associated with the tablespace are
flushed and written to the data files. You don't need to perform media recovery when you bring the tablespace and its
associated data files back online.
You can't use the ALTER TABLESPACE statement to place tablespaces offline when the database is in mount mode.
If you attempt to take a tablespace offline while the database is mounted (but not open), you receive the following
error:

ORA-01109: database not open

■ Note When in mount mode, you must use the ALTER DATABASE DATAFILE statement to take a data file offline.

When taking a tablespace offline, you can also specify ALTER TABLESPACE ... OFFLINE TEMPORARY . In this
scenario, Oracle initiates a checkpoint on all data files associated with the tablespace that are online. Oracle doesn't
initiate a checkpoint on offline data files associated with the tablespace.
You can specify ALTER TABLESPACE ... OFFLINE IMMEDIATE when taking a tablespace offline. Your database
must be in archivelog mode in this situation, or the following error is thrown:

ORA-01145: offline immediate disallowed unless media recovery enabled

When using OFFLINE IMMEDIATE , Oracle doesn't issue a checkpoint on the data files. You must perform media
recovery on the tablespace before bringing it back online.