Hello Connor & Chris :-),
A few days ago I found this fantastic SQL script checking Tablespace Free Space being written by Tom Kyte -
https://asktom.oracle.com/Misc/free.html .
So I spent some time formatting and aligning it (BTW convering
Kb into
Mb). The following is full code,
set linesize 200
set pagesize 200
column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a25 heading "Tablespace Name"
column Mbytes format 999,999,999 heading "MBytes"
column Used_Mbytes format 999,999,999 heading "Used|MBytes"
column Free_Mbytes format 999,999,999 heading "Free|MBytes"
column Largest_Mbytes format 999,999,999 heading "Largest|MBytes"
column Max_Size format 999,999,999 heading "MaxPoss|MBytes"
column pct_max_used format 999.9 heading "%|Max|Used"
break on report
compute sum of Mbytes on report
compute sum of Free_Mbytes on report
compute sum of Used_Mbytes on report
select ( select decode(extent_management,'LOCAL','*',' ') ||
decode(segment_space_management,'AUTO','a ','m ')
from dba_tablespaces
where tablespace_name = b.tablespace_name
) || nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name
, Mbytes_alloc Mbytes
, Mbytes_alloc-nvl(Mbytes_free,0) Used_Mbytes
, nvl(Mbytes_free,0) Free_Mbytes
, ((Mbytes_alloc-nvl(Mbytes_free,0))/Mbytes_alloc)*100 pct_used
, nvl(Mbytes_largest,0) Largest_Mbytes
, nvl(Mbytes_max,Mbytes_alloc) Max_Size
, decode(Mbytes_max,0,0,(Mbytes_alloc/Mbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024/1024 Mbytes_free
, max(bytes)/1024/1024 Mbytes_largest
, tablespace_name
from sys.dba_free_space
group by tablespace_name
) a,
( select sum(bytes)/1024/1024 Mbytes_alloc
, sum(maxbytes)/1024/1024 Mbytes_max
, tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024/1024 Mbytes_alloc
, sum(maxbytes)/1024/1024 Mbytes_max
, tablespace_name
from sys.dba_temp_files
group by tablespace_name
) b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/
As you can see from my code I'm wondering why it isn't using an "union all" clause to do joint query both
dba_free_space and
dba_temp_free_space on inline view "a" (however, it's using an "union all" to do joint query both
dba_data_files and
dba_temp_files on inline view "b")?
Best Regards
Quanwen Zhao
Typically there isnt a lot of "interest" or "value" in the free space in temporary tablespaces because we don't treat the segments in the same way.
If *I* allocate a large temp segment for a sort, then when I'm done, we need to clean that segment up, we just need to mark it available for anyone else to use.
Hence, it is not uncommon to see a temp tablespace always "full", it is just full of segments that can be re-used by others. If you really wanted to get a picture of temp *currently* in use, you could fold in v$tempseg_usage