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 statement 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.