Temporary Tablespace and Datafile Management in Oracle database

1.  For Finding temp file and temp tablespace with size

SELECT file#, 
       status, 
       bytes / 1024 / 1024 “Size_MB”, 
       name 
FROM   v$tempfile; 

SELECT file_name, 
       tablespace_name, 
       bytes / 1024 / 1024 / 1024, 
       status 
FROM   dba_temp_files; 

2. Add tempfile to existing temp tablespace

SQL>ALTER TABLESPACE temp ADD tempfile ‘/u02/apps/oracle/temp01_01.dbf’ SIZE 
2048m; 

3. Add new temp tablespace and make as default

SQL>CREATE TEMPORARY TABLESPACE temp2 tempfile ‘/u02/apps/oracle/temp01.dbf’ SIZE 2g autoextend ON;

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; 

Note: use “reuse” if datafile physically exists.

4. Making old Temporary Tablespace Offline

SQL>ALTER DATABASE tempfile ‘/u02/apps/oradata/temp01.dbf’ OFFLINE; 

5. Drop Temporary Tablespace 

SQL>DROP TABLESPACE temp INCLUDING CONTENTS AND datafiles;

NOTE: Don’t drop immediatly, Check user is using TEMP tablespace by the Below Given Query, then you can drop

SELECT a.username, 
       a.osuser, 
       a.sid 
       ||‘,’ 
       ||a.serial#                  SID_SERIAL, 
       c.spid                       Process, 
       b.tablespace                 tablespace, 
       a.status, 
       SUM(b.extents) * 1024 * 1024 SPACE 
FROM   v$session a, 
       v$sort_usage b, 
       v$process c, 
       dba_tablespaces d 
WHERE  a.saddr = b.session_addr 
       AND a.paddr = c.addr 
       AND b.tablespace = d.tablespace_name 
GROUP  BY a.username, 
          a.osuser, 
          a.sid 
          ||‘,’ 
          ||a.serial#, 
          c.spid, 
          b.tablespace, 
          a.status; 

6. Shrink Temporary Tablespace

SQL>ALTER TABLESPACE temp shrink tempfile ‘/u02/apps/oradata/temp01.dbf’ keep 10g;