Tablespaces monitoring

Scripts To Check Tablespace used and free space 

set lines 132

set pages 60

set feedback on

column name format a15

SELECT

a.tablespace_name,

(a.BYTES / 1024/1024)  AS total_MB,

ROUND(((a.BYTES-b.BYTES)/1024/1024) ,2) AS used_MB,

(b.BYTES / 1024/1024)  AS free_MB,

ROUND (((a.BYTES - b.BYTES) / a.BYTES) * 100, 2) AS percent_used,

ROUND (100 - ((a.BYTES - b.BYTES) / a.BYTES) * 100, 2) AS percent_free

FROM (SELECT   tablespace_name, SUM (BYTES) BYTES

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT   tablespace_name, SUM (BYTES) BYTES, MAX (BYTES) largest

FROM dba_free_space

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

union

select sh.tablespace_name,

sum(sh.bytes_used + sh.bytes_free)/1048576 total,

sum(sh.bytes_used)/1048576 used,

sum(sh.bytes_free)/1048576 free,

sum(sh.bytes_used) /sum(sh.bytes_used + sh.bytes_free) *100 percent_used,

sum(sh.bytes_free) /sum(sh.bytes_used + sh.bytes_free) *100 percent_free

from v$temp_space_header sh

group by tablespace_name

ORDER by 6;

(OR)

set linesize 121

column dummy noprint

column  pct_used format 999.9       heading "%|Used"

column  name    format a19      heading "Tablespace Name"

column  Kbytes   format 999,999,999    heading "KBytes"

column  used    format 999,999,999   heading "Used"

column  free    format 999,999,999  heading "Free"

column  largest    format 999,999,999  heading "Largest"

column  max_size format 999,999,999 heading "MaxPoss|Kbytes"

column  pct_max_used format 999.9       heading "%|Max|Used"

break   on report

compute sum of kbytes on report

compute sum of free on report

compute sum of used 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,

       kbytes_alloc kbytes,

       kbytes_alloc-nvl(kbytes_free,0) used,

       nvl(kbytes_free,0) free,

       ((kbytes_alloc-nvl(kbytes_free,0))/

                          kbytes_alloc)*100 pct_used,

       nvl(largest,0) largest,

       nvl(kbytes_max,kbytes_alloc) Mgax_Size,

       decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used

from ( select sum(bytes)/1024 Kbytes_free,

              max(bytes)/1024 largest,

              tablespace_name

       from  sys.dba_free_space

       group by tablespace_name ) a,

     ( select sum(bytes)/1024 Kbytes_alloc,

              sum(maxbytes)/1024 Kbytes_max,

              tablespace_name

       from sys.dba_data_files

       group by tablespace_name

       union all

      select sum(bytes)/1024 Kbytes_alloc,

              sum(maxbytes)/1024 Kbytes_max,

              tablespace_name

       from sys.dba_temp_files

       group by tablespace_name )b

where a.tablespace_name (+) = b.tablespace_name

order by 5 ;