How to Alter Temporary Tablespace

  • by

Some statements about temporary tablespaces are NOT valid to perform.

Create a Temporary Tablespace

You can't create a temporary tablespace like a normal, permanent tablespace.

SQL> create tablespace systemp tempfile '/u01/app/oracle/oradata/ORA19C1/SYSTEMP01.dbf' size 10m autoextend on next 10m maxsize unlimited, '/u01/app/oracle/oradata/ORA19C1/SYSTEMP02.dbf' size 10m autoextend on next 10m maxsize unlimited;
create tablespace systemp tempfile '/u01/app/oracle/oradata/ORA19C1/SYSTEMP01.dbf' size 10m autoextend on next 10m maxsize unlimited, '/u01/app/oracle/oradata/ORA19C1/SYSTEMP02.dbf' size 10m autoextend on next 10m maxsize unlimited
                          *
ERROR at line 1:
ORA-02180: invalid option for CREATE TABLESPACE

You have to use CREATE TEMPORARY TABLESPACE to explicitly declare the nature of the tablespace.

SQL> create temporary tablespace systemp tempfile '/u01/app/oracle/oradata/ORA19C1/SYSTEMP01.dbf' size 10m autoextend on next 10m maxsize unlimited, '/u01/app/oracle/oradata/ORA19C1/SYSTEMP02.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace created.

ORA-00940: invalid ALTER command

After the temporary tablespace has been created, it's totally different. In the following case, we tried to add a tempfile to the temporary tablespace.

SQL> alter temporary tablespace systemp add tempfile '/u01/app/oracle/oradata/ORA19C1/SYSTEMP03.dbf' size 10m autoextend on next 10m maxsize unlimited;
alter temporary tablespace systemp add tempfile '/u01/app/oracle/oradata/ORA19C1/SYSTEMP03.dbf' size 10m autoextend on next 10m maxsize unlimited
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

Or offline a temporary tablespace.

SQL> alter temporary tablespace temp offline;
alter temporary tablespace temp offline
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

Alter a Temporary Tablespace

Since the database has already knew the nature of a named tablespace, we don't and can't specify TEMPORARY to alter the temporary tablespace. Instead, we use ALTER TABLESPACE, a normal tablespace altering clause.

SQL> alter tablespace systemp add tempfile '/u01/app/oracle/oradata/ORA19C1/SYSTEMP03.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace altered.

That is to say, there's no ALTER TEMPORARY TABLESPACE SQL statement in Oracle database.

Another example of altering a temporary tablespace is to offline a temporary tablespace.

Leave a Reply

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