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

Comments