CREATE, DROP or ADD Tempfile in Oracle Database

CREATE, DROP or ADD Temporary datafile in Oracle Database

Managing Temporary tablespace in Oracle Database

We all are aware of the TEMP Tablespace which is a very useful part of oracle database to perform all the temporary operations liking sorting etc. Below shown are some commands to manage these temporary tablespaces or datafiles:

To view information about Temporary Tablespaces and Tempfiles,

SQL>select * from dba_temp_files;
SQL>select * from v$tempfile;

To view information about free space in tempfiles,

SQL>select * from V$TEMP_SPACE_HEADER;

To create a new Temporary Tablespace,

SQL> create temporary tablespace TEMP tempfile '/u02/database/mydb/temp_01.dbf' SIZE 500m autoextend on next 10M maxsize unlimited;

To drop an existing Temporary Tablespace,

SQL> Drop tablespace TEMP including contents and datafiles;

To add Tempfile in Temporary Tablespace,

SQL> Alter tablespace temp add tempfile '/u02/database/mydb/temp_02.dbf' size 50m reuse autoextend on next 1m maxsize 500m;