In Oracle 9i or above, we can create default temporary tablespace. By default, if user doesn’t have any temporary tablespace set, it will automatically use this default temporary tablespace.
Before setting up the default temporary tablespace, you have to create the temporary tablespace:
SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE '/oradata/tempfile_01.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
The tablespace has to be ‘Temporary’ type.
The command to set the default temporary tablespace:
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
And to see if the default temporary tablespace has been configured correctly:
SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
You’ll see the temporary tablespace there.
