Temporary Tablespace managed 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;