Query monitoraggio tablespace

Di seguito due query per individuare lo stato di occupazione dei tablespace. 

max_ts_size è lo spazio massimo disponibile (MB)

max_ts_pct_used è la percentuale di spazio disponibile usato cioè  la % di occupazione sul totale disponibile.  Questa % è stata calcolata considerando l’autoextend dei datafile. E’ quella da considerare per aggiungere un tbs. Se ragguinge il 97%, ad esempio, si potrebbe aggiungere un nuovo datafile. 

curr_ts_size è la dimensione attualmente allocata (MB)

used_ts_size lo spazio attualmente usato (MB)

ts_pct_used è la percentuale di spazio allocato usato

free_ts_size spazio libero allocato (MB)

ts_pct_free  è la percentuale di spazio allocato libero

set pagesize 200 verify off sqlprompt "" term off entmap off echo off
set markup html on spool on
spool TS_info_emen_2.html
 SELECT df.tablespace_name tablespace_name,
  max(df.autoextensible) auto_ext,
  round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
  round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
  round(df.bytes / (1024 * 1024), 2) curr_ts_size,
  round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
  round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
  round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
  nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
 FROM dba_free_space fs,
  (select tablespace_name,
  sum(bytes) bytes,
  sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
  max(autoextensible) autoextensible
  from dba_data_files
  group by tablespace_name) df
 WHERE fs.tablespace_name (+) = df.tablespace_name
-- and df.tablespace_name like '%EME%' 
 GROUP BY df.tablespace_name, df.bytes, df.maxbytes
 UNION ALL
 SELECT df.tablespace_name tablespace_name,
  max(df.autoextensible) auto_ext,
  round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
  round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
  round(df.bytes / (1024 * 1024), 2) curr_ts_size,
  round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
  round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
  round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
  nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
 FROM (select tablespace_name, bytes_used bytes
  from V$temp_space_header
  group by tablespace_name, bytes_free, bytes_used) fs,
  (select tablespace_name,
  sum(bytes) bytes,
  sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
  max(autoextensible) autoextensible
  from dba_temp_files
  group by tablespace_name) df
 WHERE fs.tablespace_name (+) = df.tablespace_name
 --and df.tablespace_name like '%EME%' 
 GROUP BY df.tablespace_name, df.bytes, df.maxbytes
 ORDER BY 4 DESC
/
set echo off
set sqlprompt "SQL> " term on
set verify on
spool off
set markup html off spool off
set echo on
exit

Seconda query meno precisa della precedente ma comunque valida.

select tbs.tablespace_name
,tot.bytes/1024/1024 "DIMENSIONE ALLOCATA (mb)"
,tot.bytes/1024/1024-sum(nvl(fre.bytes,0))/1024/1024 "SPAZIO USATO(mb)"
,sum(nvl(fre.bytes,0))/1024/1024 "SPAZIO LIBERO ALLOCATO(mb)"
,(1-sum(nvl(fre.bytes,0))/tot.bytes)*100 "SPAZIO ALLOCATO USATO(%)"
, round(met.used_percent,2) "SPAZIO DISPONIBILE USATO(%)" -- spazio totale che considera anche gli autoextend
,decode(greatest( round(met.used_percent,2), 97),97, '', 'warning' ) pct_warn
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs,
dba_tablespace_usage_metrics met
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
and met.tablespace_name = tbs.tablespace_name
--and tot.tablespace_name ='TS_ALFA_IDX'
group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes,met.used_percent
order by 6 desc;

Lo stesso risultato si ha vedendo Enterprise Manager: Amministrazione >Memoria> Tablespace