How to Take Temporary Tablespaces Offline

  • by
Basically, you can't take 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

Instead, you should take each temp file offline.
SQL> alter database tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf' offline;

Database altered.

If the temporary tablespace is not the default one. You 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. You 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

Therefore, I may say that it's an equivalent effect of taking temporary tablespaces offline.

Leave a Reply

Your email address will not be published. Required fields are marked *