How to Create Temporary Tablespace or Add /Drop tempfile

How to Create Temporary Tablespace or Add tempfile to Temp tablespace

The following issues may occur with Temporary tablespaces in Oracle

1. Monitoring
2. Running out of Space.

3. Resizing

4. Changing the default temporary tablespace for the database.

5.Dropping a tempfile

With the following command the temporaty tablesapces can be monitored for usage and space

select * from dba_temp_files order by tablespace_name;

select * from v$tempfile;

Adding a tempfile with 1Gb size to the existing temp tablespce

ALTER TABLESPACE temp ADD TEMPFILE '/usr1/temp1.dbf' size 1000M reuse;

ALTER TABLESPACE temp ADD TEMPFILE '<path>/temp1.dbf' size 1G reuse;

Dropping temfile

ALTER DATABASE TEMPFILE '<path>/temp1.dbf' DROP INCLUDING DATAFILES;

clause DROP INCLUDING DATAFILES -> This will drop the tempfile and removes the file in the O/S also.

Changing the default temporary tablespace for the database or reclaiming the space occupied by the current temporary tablespace

The following procedure to be followed

a. create another temporary tablespace in another drive ( windows ) or filesystem.(linux)

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '<PATH>/temp1_01.dbf' size 10g;

b. Make it default for the database.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

c. Drop the current temporary tablespace

ALTER DATABASE TEMPFILE '<path>/temp1.dbf' DROP INCLUDING DATAFILES;