How to Manage Temporary Tablespaces in Oracle

Temporary tablespace is used for sorting operation in Oracle database during select queries operations. Cannot have objects in it permanently its all temporary stored for operations.

Create Syntax of Temporary tablespace

CREATE TEMPORARY TABLESPACE temp TEMPFILE 'E:\oradata\orcl\temp01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

Make Default Temporary Tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Check the temporary tablespace

Select file_name, tablespace_name from dba_temp_files;

Altering the Temporary tablespace

ALTER TABLESPACE temp ADD TEMPFILE 'E:\oradata\orcl\temp02.dbf' SIZE 2M;

Offline the temporary tablespace

ALTER TABLEPSACE TEMPFILE 'E:\oradata\orcl\temp01.dbf' OFFLINE;

Online the temporary tablespace

ALTER TABLEPSACE TEMPFILE 'E:\oradata\orcl\temp01.dbf' ONLINE;

Drop the existing temporary tablespace datafile

ALTER DATABASE TEMPFILE 'E:\oradata\orcl\temp02.dbf' DROP INCLUDING DATAFILES;

Assign default temporary tables based on users

ALTER USER user1 TEMPORARY TABLESPACE temp;

Drop temporary tablespace

DROP TABLESPACE temp;