Change Default Temporary Tablespace in Oracle

Being Oracle DBA we all are well known about the tablespace. Oracle RDBMS have multiple kinds of a tablespace with a different kind of purpose. We are going to have look on steps use for “Change Default Temporary Tablespace”.
Check the current 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

Create another TEMP tablespace

SQL> create temporary tablespace temp2 tempfile 'D:\oracle\product\10.2.0\oradata\dba_01\TEMP02.DBF' SIZE 2M;
Tablespace created.

Stop the database and remove tempfile

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Delete TEMP01.DBF <<<<

Start the database and change the TEMP tablespace

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 temp02;
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        TEMP02 

Today’s thought

“You have to believe in what you do in order to get what you want.” Larry Ellison

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp