Change Default Temporary Tablespace

SQL> column property_value format a25

SQL> SELECT property_name,property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE

------------------------------ -------------------------

DEFAULT_TEMP_TABLESPACE TEMP

SQL> create temporary tablespace temp2

tempfile 'C:\oracle\product\10.2.0\oradata\dba20\TEMP02.DBF' SIZE 2M;

Tablespace created.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Delete TEMP01.DBF

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1247876 bytes

Variable Size 79693180 bytes

Database Buffers 79691776 bytes

Redo Buffers 7139328 bytes

Database mounted.

Database opened.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> column property_value format a25

SQL> SELECT property_name,property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE

------------------------------ ----------------------------------------------

DEFAULT_TEMP_TABLESPACE TEMP2