What is Temporary Tablespace
A temporary tablespace is used for sorting operations in Oracle. It is also used in the join operations. Basically, it is temporary storage and temporary objects are created here. Global temporary tables are also created in this tablespace. This is quite a significant tablespace and can pose issues if not managed correctly. Let’s see various queries for the Management of Temporary tablespace
How to create temp tablespace in Oracle
Here is the SQL to create the temp tablespace
Single tempfile CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oracle/TEST/oradata/temp_1.dbf' SIZE 1000M; Multiple tempfile CREATE TABLESPACE TEMP TEMPFILE '/u01/oracle/TEST/oradata/temp_1.dbf' SIZE 1000M '/u01/oracle/TEST/oradata/temp_2.dbf' SIZE 1000M ; With ASM CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 1000M;
How to check temp tablespace in Oracle
Oracle database can have more than one temporary tablespace
select tablespace_name,status,contents from dba_tablespaces where contents='TEMPORARY';
We can select the files in each of these tablespace using the below query
select file_name, tablespace_name, bytes,autoextensible from dba_temp_files or select a.name,b.name, a.bytes from v$tempfile a, v$tablespace b where a.ts#=b.ts#
How to check the size of the Temp tablespace
Here is the query on how to check the temp tablespace size in GB in Oracle
select tablespace_name,sum(bytes)/1024/1024/1024 temp_gb
from dba_temp_files group by tablespace_name;
or
select b.name, sum(a.bytes)/1024/1024/1024 from v$tempfile a, v$tablespace b where a.ts#=b.ts# group by b.name;
If you want to calculate in MB, then use the below query
select tablespace_name,sum(bytes)/1024/1024 temp_mb from dba_temp_files group by tablespace_name; or select b.name, sum(a.bytes)/1024/1024 from v$tempfile a, v$tablespace b where a.ts#=b.ts# group by b.name;
How to check free space in temp tablespace in Oracle
Here is the query for that
select tsh.tablespace_name,
dtf.bytes/1024/1024/1024 total_GB,
sum(nvl(tsh.bytes_used,0))/1024/1024/1024 used_GB,
sum(nvl(tsh.bytes_free,0))/1024/1024/1024 free_GB,
(1-sum(nvl(tsh.bytes_free,0))/dtf.bytes)*100 pct,
from v$temp_space_header tsh,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) dtf
where dtf.tablespace_name = tsh.tablespace_name(+)
group by tsh.tablespace_name, dtf.bytes/1024, dtf.bytes
order by 1
/
With 11g, A new view dba_temp_free_space is introduced, so we can use that also as
SELECT * FROM dba_temp_free_space;
How to check Temporary tablespace usage at the instance level
select inst_id, tablespace_name, segment_file, total_blocks, used_blocks, free_blocks, max_used_blocks, max_sort_blocks from gv$sort_segment; select inst_id, tablespace_name, blocks_cached, blocks_used from gv$temp_extent_pool; select inst_id,tablespace_name, blocks_used, blocks_free from gv$temp_space_header; select inst_id,free_requests,freed_extents from gv$sort_segment;
How to check the usage percentage of Temp tablespace
select tablespace_name, ' %free='|| round(sum(free_blocks) / sum(total_blocks) * 100 ,0)
from gv$sort_segment group by tablespace_name;
How to find the session which is using temporary tablespace
set pagesize 50000 echo off verify off feedback off lines 80 heading on col username format a15 head 'Username' col osuser format a15 head 'OS User' col sid format 99999 head 'Sid' col logon_time format a14 head 'Logon Time' col tablespace format a15 head 'Tablespace Name' select tablespace , a.osuser , a.username , a.sid , to_char(logon_time,'MM/DD/YY HH24:MI') logon_time from v$session a, v$sort_usage b where a.saddr = b.session_addr; or SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 180 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN instance_name FORMAT a9 HEADING ‘Instance’ COLUMN tablespace_name FORMAT a15 HEADING ‘Tablespace Name’ COLUMN sid FORMAT 99999 HEADING ‘SID’ COLUMN serial_id FORMAT 99999999 HEADING ‘Serial ID’ COLUMN session_status FORMAT a9 HEADING ‘Status’ COLUMN oracle_username FORMAT a18 HEADING ‘Oracle User’ COLUMN os_username FORMAT a18 HEADING ‘O/S User’ COLUMN os_pid FORMAT a8 HEADING ‘O/S PID’ COLUMN session_terminal FORMAT a10 HEADING ‘Terminal’ TRUNC COLUMN session_machine FORMAT a30 HEADING ‘Machine’ TRUNC COLUMN session_program FORMAT a20 HEADING ‘Session Program’ TRUNC COLUMN contents FORMAT a9 HEADING ‘Contents’ COLUMN extents FORMAT 999,999,999 HEADING ‘Extents’ COLUMN blocks FORMAT 999,999,999 HEADING ‘Blocks’ COLUMN bytes FORMAT 999,999,999,999 HEADING ‘Bytes’ COLUMN segtype FORMAT a12 HEADING ‘Segment Type’ BREAK ON instance_name SKIP PAGE SELECT i.instance_name instance_name , t.tablespace tablespace_name , s.sid sid , s.serial# serial_id , s.status session_status , s.username oracle_username , s.osuser os_username , p.spid os_pid , s.program session_program , t.contents contents , t.segtype segtype , (t.blocks * c.value) bytes FROM gv$instance i , gv$session s , gv$process p , gv$sort_usage t , (select value from v$parameter where name = ‘db_block_size’) c WHERE s.inst_id = p.inst_id AND p.inst_id = i.inst_id AND t.inst_id = i.inst_id AND s.inst_id = i.inst_id AND s.saddr = t.session_addr AND s.paddr = p.addr ORDER BY i.instance_name , s.sid;
How to check the temp usage by Session
SELECT s.sid, s.username, s.status, u.tablespace, u.segfile#, u.contents, u.extents, u.blocks FROM v$session s, v$sort_usage u WHERE s.saddr=u.session_addr ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;
If we want to see the sql being run also, then
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text FROM v$session a, v$tempseg_usage b, v$sqlarea c WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value ORDER BY b.tablespace, b.blocks;
How to find sessions using a lot of TEMP
col inst_id format 999 col sid format 99999 col status format a11 col program format a20 trunc col machine format a17 trunc col action format a39 col module format a39 col blocks heading "TEMP BLOCKS" SELECT b.blocks, s.inst_id, s.sid, s.status, s.action, s.program, s.machine, s.module FROM gv$session s, gv$sort_usage b WHERE s.saddr = b.session_addr and s.inst_id = b.inst_id and b.blocks > 100000 order by b.blocks desc;
How to increase temp tablespace in Oracle
We can increase the temp tablespace by either extending the existing file or we can add the new temp file.
alter database tempfile '+DATA/test/tempfile01.dbf' resize 10000m ; or alter tablespace TEMP add tempfile '+DATA/test/tempfile02.dbf' resize 10000m ;
How to resize the tempfile
alter database tempfile '+DATA/test/tempfile01.dbf' resize 10000m ;
How to drop the tempfile
It is not possible to drop the datafile from the Normal Tablespace but it is possible to drop the tempfile from the temporary tablespace. Here is the sql
ALTER DATABASE TEMPFILE '<temp file>' DROP INCLUDING DATAFILES;
As for the users who were using the temp file, their transaction will end and will be greeted with the following error message:
ERROR at line 4: ORA-00372: file 601 cannot be modified at this time ORA-01110: data file 601: '<temp file>' ORA-00372: file 601 cannot be modified at this time ORA-01110: data file 601: '<temp file>'
If this happens, you should attempt to drop the temp file again so the operation is successful:
SQL> ALTER DATABASE TEMPFILE '<temp file>' DROP INCLUDING DATAFILES; Database altered.
How to drop temp tablespace in oracle
We can drop the temp tablespace using the below command
drop tablespace including contents and datafiles;
Important View for temp information
How to change the default temporary tablespace of the user
alter user <user name> default temporary tablespace <TEMP NAME>;
How to change the default Temp tablespace of the Database
SQL> SELECT property_name,property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE ------------- --------------- DEFAULT_TEMP_TABLESPACE TEMP alter database default temporary tablespace temp2; SQL> SELECT property_name,property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE ------------ ------------- DEFAULT_TEMP_TABLESPACE TEMP2
I hope you like queries on How to check temp tablespace in Oracle, How to create temp tablespace in Oracle,How to drop temp tablespace in Oracle,how to check free space in temp tablespace in Oracle
Also Reads
Oracle Create Tablespace: This article on how to create tablespace in oracle, various characteristics associated with it and different create tablespace statements
ORA-01652:ORA-01652 error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in oracle database. Check out how to resolve it
How to check Tablespace in Oracle: Learn about How to Check Tablespace in Oracle, tablespace free space,tablespace growth information,tablespace size ,associated datafiles ,tablespace size
Automatic Workload Repository: Learn about Automatic Workload Repository(AWR).About views, table, how to purge information, how to collect and frequency of collection
Oracle database administration tutorial: This lists all the Oracle DBA-related stuff. Very helpful for administration
how to move lob segment: how to move lob segment from one tablespace to another, how to reclaim space after major deleted in lob segment
how to get table definition in oracle: Check out how to get table definition in oracle, oracle show index definition, get ddl of a materialized view in oracle, get the query of a view in oracle
https://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_75a.htm