How to Alter Temporary Tablespace Offline

  • by

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

Leave a Reply

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