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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s