As the name “temporary” itself confronts that its a temporary storage used for sorting,computations, processing. Late 90’s “system” tablespace is being used as temporary tablespace for all the users which is highly not recommended since it brings down the performance of the database drastcially. “System” tablespace is the storge where metadata of the database is being stored and processed. Later Oracle has made a provision to specify a tablespace as an ” Temporary tablespace” while creating a user or with an ALTER user statement. But what happens when a user has been created with out any temporary tablespace specification. Indirectly he would be using the system tablespace which is not recommended. To avoid this situation oracle has introduced “Default Temporary tablespace”. A “Default Temporary Tablespace” can be created for an entire database.  Incase if you forgot to specify the temporary tablespace for an user, storage area specified as Default temporary tablespce would be used.

 Creating a Temporary tablespace

CREATE TEMPORARY TABLESPACE temp01;

Creating a Temporary tablespace with destination file name and Size Limitation

CREATE TEMPORARY TABLESPACE temp02
                 TEMPFILE ‘C:\ORACLE\dATA\temp\ts02.tmp’ SIZE 200M;

Check the above created temp files in data dictionary 

SQL> select tablespace_name, file_name, (bytes/1024/1024)  from dba_temp_files;TABLESPACE_NAME   FILE_NAME                                                                                                  BYTES/1024/102
—————– ————————————————                                                                                   ————–
TEMP01            C:\ORACLE\DATA\temp\O1_TEMP01_ZR0VCW00_.TMP                                200
TEMP02            C:\ORACLE\DATA\temp\TS02.TMP                                                                                 200

Specify the Default Temporary tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;

Users being automatically assigned with temporary tablespace

SQL> CREATE USER tempuser IDENTIFIED BY tempuser;
User created.SQL> select username,temporary_tablespace from dba_users where username = ‘TEMPUSER’;
USERNAME                       TEMPORARY_TABLESPACE
—————————— ——————————
TEMPUSER                       TEMP01SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp02;
Database altered.SQL> select username,temporary_tablespace from dba_users where username = ‘TEMPUSER’;
USERNAME                       TEMPORARY_TABLESPACE
—————————— ——————————
TEMPUSER                       TEMP02