SQL QUERY TO FIND TEMP TABLESPACE USAGE ORACLE
Use the query to find the free space in temporary tablespace on oracle database
col tablespace_name format a20
col bytes format 999999 justify center
col BYTES_USED format 999999 justify center
col BYTES_FREE format 999999 justify center
select x.tablespace_name,sum(x.bytes/1048576) as TOTAL_SPACE,
sum(y.BYTES_USED/1048576) as USED_SPACE,
sum(y.BYTES_FREE/1048576) FREE_SPACE
from dba_temp_files x
inner join v$temp_space_header y on x.FILE_ID=y.FILE_ID
group by x.tablespace_name,x.FILE_ID
order by x.FILE_ID;
Temp free space:
col TABLESPACE_NAME format a10
col TABLESPACE_SIZE format a20
col TABLESPACE_SIZE format 999999
col ALLOCATED_SPACE format 999999
col FREE_SPACE format 999999
select TABLESPACE_NAME,TABLESPACE_SIZE/1048576 MB_TOT,ALLOCATED_SPACE/1048576 MB_USED,FREE_SPACE/1048576 MB_FREE from dba_temp_free_space;OUTPUT:
kish@exdbx<>col tablespace_name format a20
kish@exdbx<>col bytes format 999999 justify center
kish@exdbx<>col BYTES_USED format 999999 justify center
kish@exdbx<>col BYTES_FREE format 999999 justify center
kish@exdbx<>select x.tablespace_name,round(x.bytes/1073741824,3) as TOTAL_SPACE,round(y.BYTES_USED/1073741824,3) as USED_SPACE,round(y.BYTES_FREE/1073741824,3) as FREE_SPACE from dba_temp_files x inner join v$temp_space_header y on x.FILE_ID=y.FILE_ID order by x.FILE_ID;
TABLESPACE_NAME TOTAL_SPACE USED_SPACE FREE_SPACE
-------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
TEMP .104 .104 0
Temp Freespace:
kish@exdbx<>col TABLESPACE_NAME format a10
kish@exdbx<>col TABLESPACE_SIZE format a20
kish@exdbx<>col TABLESPACE_SIZE format 999999
kish@exdbx<>col ALLOCATED_SPACE format 999999
kish@exdbx<>col FREE_SPACE format 999999
kish@exdbx<>select TABLESPACE_NAME,TABLESPACE_SIZE/1048576 MB_TOT,ALLOCATED_SPACE/1048576 MB_USED,FREE_SPACE/1048576 MB_FREE from dba_temp_free_space;
TABLESPACE MB_TOT MB_USED MB_FREE
---------- ---------------------------------------- ---------------------------------------- ----------------------------------------
TEMP 355 355 336