How to create, add, drop, reuse Tempfile

 Temporary tablespaces are used to manage space for database sort and joining operations and for storing global temporary tables. For joining two large tables or sorting a bigger result set, Oracle cannot do in memory by using SORT_AREA_SIZE in PGA (Programmable Global Area). Space will be allocated in a temporary tablespace for doing these types of operations. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, SELECT DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

Note that a temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up. A temporary tablespace contains schema objects only for the duration of a session.

TEMP FILE :

Unlike normal datafiles, temporary files are not fully allocated. When you create a tempfiles, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a tempfiles than to create a normal datafile.

Tempfiles are not recorded in the database's control file. This implies that just recreate them whenever you restore the database, or after deleting them by accident. You can have different tempfile configurations between primary and standby databases in dataguard environment, or configure tempfiles to be local instead of shared in a RAC environment.

One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a tempfile from a database. 

Below are the scripts to Manage Temporary Tablespace in oracle database.

1. To Create new temporary tablespace:

SQL> CREATE TEMPORARY TABLESPACE TEMP1
TEMPFILE '/ua1001/EBS12.2/oradata/PRODEBS/temp01.dbf' SIZE 1G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

2. How to Add tempfile to TEMP1 tablespace:

SQL> ALTER TABLESPACE TEMP1 ADD TEMPFILE '/ua1001/EBS12.2/oradata/PRODEBS/temp01.dbf' SIZE 3G AUTOEXTEND ON NEXT 300M;

3.How to resize existing Tempfile :

SQL> ALTER DATABASE TEMPFILE '/ua1001/EBS12.2/oradata/PRODEBS/temp01.dbf' RESIZE 5G;


4.How to Drop Tempfile :

SQL> ALTER DATABASE TEMPFILE '/ua1001/EBS12.2/oradata/PRODEBS/temp01.dbf' DROP;

If you drop tempfile by using above command ,tempfile temp01.dbf exists physically .
We can reuse same file using below command.

5.How to Add the dropped file back and it gets created

SQL> ALTER TABLESPACE TEMP2 ADD TEMPFILE
'/ua1001/EBS12.2/oradata/PRODEBS/temp01.dbf' SIZE 3G
REUSE AUTOEXTEND OFF;

5.Drop tempfile including datafiles.

SQL> ALTER DATABASE TEMPFILE '/ua1001/EBS12.2/oradata/PRODEBS/temp01.dbf' DROP INCLUDING DATAFILES;

How to drop and recreate TEMP Tablespace :
​
Steps:

1.Create new Temporary Tablespace.
2.Make the default tablespace to newly created temp tablespace.
3.Check any sessions using temp tablespace.
4.Kill sessions using Temp Tablespace.
5.Drop temp tablespace TEMP1 (Old tablespace).
6.Recreate Tablespace TEMP1.
7.Make the default tablespace to TEMP1.
8.Drop temporary tablespace which you created at step 1.

1.Create new Temporary Tablespace.

SQL> create temporary tablespace NEWTEMP1 tempfile '/ua1001/EBS12.2/oradata/PRODEBS/NEWtemp01.dbf' size 10G;


If You are using ASM then use diskgroup name.

SQL> show parameter db_create_file_dest;
SQL> create temporary tablespace temp1 tempfile '+DATA1' size 10G; 


2.Make the default tablespace to newly created temp tablespace.

SQL> alter database default temporary tablespace NEWTEMP1;

You can run the following query to see that the default temporary tablespace.


SQL> SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

                        OR 

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200SELECT * FROM database_properties WHERE property_name like ‘%TABLESPACE’;


3.Check any sessions using temp tablespace.

SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;


                         OR 

SQL> SELECT s.sid,s.serial#,osuser,process,program,s.sql_hash_value,u.extents,
u.blocks FROM v$session s, v$sort_usage u WHERE s.saddr = u.session_addr 
order by extents,blocks asc;

How to get the SQL ID and SQL Text:

SQL> select USERNAME,SID,SQL_ID from v$session where sid=2678;


Confirm the temp tablespace that this user is using.

SQL> select temporary_tablespace from dba_users where username like 'W499';

4.Kill sessions using Temp Tablespace

SQL> alter system kill session 'sid,serial#' immediate;

5.Drop temp tablespace TEMP1 (Old tablespace)

SQL> drop tablespace TEMP1 INCLUDING CONTENTS AND DATAFILES;

6.Recreate Tablespace TEMP1.

SQL> CREATE TEMPORARY TABLESPACE TEMP1
TEMPFILE '/ua1001/EBS12.2/oradata/PRODEBS/temp01.dbf' SIZE 10G;

7.Make the default tablespace to TEMP1
​
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

8.Drop temporary tablespace which you created at step 1.

SQL> drop tablespace NEWTEMP1 INCLUDING CONTENTS AND DATAFILES;

How to check status of Tempfile :

SQL> SELECT file_name, status FROM dba_temp_files;

SQL> select file_name, file_id, tablespace_name, bytes/1024/1024 mb from dba_temp_files;


To check Temp tablesapce size .

SELECT a.tablespace_name,ROUND((c.total_blocks*b.block_size)/1024/1024/1024,2)
"Total Size [GB]",ROUND((a.used_blocks*b.block_size)/1024/1024/1024,2) "Used_size[GB]",
ROUND(((c.total_blocks-a.used_blocks)*b.block_size)/1024/1024/1024,2) "Free_size[GB]",
ROUND((a.max_blocks*b.block_size)/1024/1024/1024,2) "Max_Size_Ever_Used[GB]",            
ROUND((a.max_used_blocks*b.block_size)/1024/1024/1024,2) "MaxSize_ever_Used_by_Sorts[GB]" ,
ROUND((a.used_blocks/c.total_blocks)*100,2) "Used Percentage"
FROM V$sort_segment a,dba_tablespaces b,(SELECT tablespace_name,SUM(blocks)
total_blocks FROM dba_temp_files GROUP by tablespace_name) c
WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name;

SELECT tablespace_name, file_name, bytes/1024/1024/1024 FROM dba_temp_files WHERE tablespace_name ='TEMP1';

Run the below query to see which session is using more Temp Space :

SELECT s.sid,s.serial#,osuser,process,program,s.sql_hash_value,u.extents,
u.blocks FROM v$session s, v$sort_usage u WHERE s.saddr = u.session_addr 
order by extents,blocks asc;


Temporary Tablespace views :

DBA_TEMP_FILES
DBA_DATA_FILES
DBA_TABLESPACES
DBA_TEMP_FREE_SPACE     (Oracle 11g)
V$TEMPFILE
V$TEMP_SPACE_HEADER
V$TEMPORARY_LOBS
V$TEMPSTAT
V$TEMPSEG_USAGE