Offline a Temporary Tablespace
Theoretically, you can't take the whole temporary tablespaces offline.
SQL> alter tablespace temp offline;
alter tablespace temp offline
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
ORA-03217 told us that we can't take a whole temporary tablespace offline. This is true. According to Oracle documentation, we see something about taking a temporary tablespace offline.
You cannot take a temporary tablespace offline. Instead, you take its temp file offline. The view V$TEMPFILE displays online status for a temp file.
Obviously, there's no way to offline a whole temporary tablespace, we need to work with tempfiles so as to work around it.
Please note that, there's no ALTER TEMPORARY TABLESPACE such statement in Oracle database. So I don't event try it.
Offline a Tempfile
Instead, we can take one tempfile offline at a time by ALTER DATABASE.
SQL> alter database tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf' offline;
Database altered.
Offline All Tempfiles
We cannot offline all tempfiles of the default temporary tablespace.
SQL> alter tablespace temp tempfile offline;
alter tablespace temp tempfile offline
*
ERROR at line 1:
ORA-12905: default temporary tablespace cannot be brought OFFLINE
If you really want to offline it, you may switch the default temporary tablespace to another one at database level, then do it.
For a non-default temporary tablespace, we can take all temp files offline in the tablespace like this:
SQL> alter tablespace erp_temp tempfile offline;
Tablespace altered.
Although the status of tablespace is still online, the status of temp files are all offline at only one command. The best thing is that we don't have to take temp files offline one by one.
SQL> select name, status from v$tempfile where name like '%erp_temp%.dbf';
NAME STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/ORCL112/erp_temp01.dbf OFFLINE
/u01/app/oracle/oradata/ORCL112/erp_temp02.dbf OFFLINE
/u01/app/oracle/oradata/ORCL112/erp_temp03.dbf OFFLINE
/u01/app/oracle/oradata/ORCL112/erp_temp04.dbf OFFLINE
/u01/app/oracle/oradata/ORCL112/erp_temp05.dbf OFFLINE
/u01/app/oracle/oradata/ORCL112/erp_temp06.dbf OFFLINE
/u01/app/oracle/oradata/ORCL112/erp_temp07.dbf OFFLINE
/u01/app/oracle/oradata/ORCL112/erp_temp08.dbf OFFLINE
/u01/app/oracle/oradata/ORCL112/erp_temp09.dbf OFFLINE
/u01/app/oracle/oradata/ORCL112/erp_temp10.dbf OFFLINE
10 rows selected.
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name in ('ERP_TEMP');
TABLESPACE_NAME STATUS
------------------------------ ---------
ERP_TEMP ONLINE
A real practice that needs to take some tempfiles offline is to change temp files location.