Blog

Blog

Espace occupé par une base de données Oracle

je compile ici deux requêtes très intéressante pour surveiller l'espace occupé par une base de données Oracle.

ASM

Si vous êtes sous ASM, vous pouvez surveiller l'espace occupé sur chaque diskgroup (Les données étant load balancées, je ne vois pas trop l'intérêt de travailler sur l'espace occupé dans chaque disque).
SET LINES 200
SET PAGES 100
COL dg_name FORMAT A20
COL d_name FORMAT A20
COL action FORMAT A20
COL etat FORMAT A20
COL path FORMAT A30
COL size_mb FORMAT 9999
COL free_mb FORMAT 9999

SELECT NVL(adg.name,'n/a') dg_name,
ROUND(SUM(ad.TOTAL_MB/1024)) SIZE_GB,
ROUND(SUM(ad.FREE_MB/1024)) FREE_GB,
ROUND(SUM(ad.TOTAL_MB/1024))-ROUND(SUM(ad.FREE_MB/1024)) USED_GB
FROM v$asm_diskgroup adg LEFT JOIN v$asm_disk ad ON ad.group_number = adg.group_number
WHERE ad.header_status <> 'foreign'
GROUP BY NVL(adg.name,'n/a')

Tablespaces

Si vous n'êtes pas sous ASM, l'espace pris sur le disque n'est pas un bon indicateur étant donné que l'espace alloué dans les tablespaces est considéré par l'OS comme occupé alors qu'Oracle peut très bien ne pas l'utiliser...

J'ai récupéré cette requête auprès d'une collègue qui elle même la tenait d'un collègue etc... Bref, je ne connais pas l'auteur de cette requête s'il se reconnaît et désire que je la supprime, il lui suffit de me contacter...
set pagesize 1000
set linesize 150

select  SUBSTR(ORA_DATABASE_NAME,0,32) as DATABASE_NAME,
SUBSTR(HOST_NAME,0,32) as HOST_NAME,
USER,
to_char(SYSDATE,'YYYY-MM-DD hh24 :mi') as CUR_DATE
from v$instance ;


set echo off ;



col STATUT       for A20        head "STATUT"             wrap
col tbsName      for A25        head "TABLESPACE"         wrap
col prmType      for A1         head "T"                  trunc
col extMan       for A1         head "X"                  trunc
col tbsStat      for A2         head "On"                 trunc
col tbsMsize     for 999,990    head "TAILLE|Allouée|(M)" trunc
col tbsMsizeMax  for 999,990    head "TAILLE|Limite|(M)"  trunc
col usedSize     for 999,990    head "VOLUME|Utilise|(M)" trunc
col usedPct      for 999.99     head "VOLUME|/alloué|(%)" trunc
col usedTotPct   for 999.99     head "VOLUME|/limite|(%)" trunc

select
--decode( greatest(90,nvl(t.BYTES/a.BYTES*100,0)), 90, 'TAILLE_TBS_OK', 'WARNING_TBS_FULL' ) STATUT,
case
when d.TABLESPACE_NAME like 'SYS%'                            then '   NO_CHECK'
when 
case
when nvl(w.BYTES /1024/1024, 0) != 0 then nvl((a.BYTES - NVL(f.BYTES, 0)) / nvl(w.BYTES, 0) * 100, 0)
else nvl((a.BYTES - NVL(f.BYTES, 0)) / a.BYTES * 100, 0)
end > 95
then '!! ALERT_TBS   > 95%'
when 
case
when nvl(w.BYTES /1024/1024, 0) != 0 then nvl((a.BYTES - NVL(f.BYTES, 0)) / nvl(w.BYTES, 0) * 100, 0)
else nvl((a.BYTES - NVL(f.BYTES, 0)) / a.BYTES * 100, 0)
end > 85
then '!  WARNING_TBS > 85%'
else '   TBS_SIZE_OK'
end STATUT,
d.TABLESPACE_NAME                                   tbsName,
decode (d.CONTENTS, 'PERMANENT', 'P',
'TEMPORARY', 'T',
'UNDO', 'U')               prmType,
decode (d.EXTENT_MANAGEMENT, 'LOCAL', 'L', 'D')     extMan,
decode (d.STATUS, 'ONLINE', ' Y', ' N')             tbsStat,
nvl(a.BYTES /1024/1024, 0)                          tbsMsize,
nvl(w.BYTES /1024/1024, 0)                       tbsMsizeMax,
nvl(a.BYTES - nvl(f.BYTES, 0), 0)/1024/1024         usedSize,
nvl((a.BYTES - NVL(f.BYTES, 0)) / a.BYTES * 100, 0) usedPct,
case
when nvl(w.BYTES /1024/1024, 0) != 0 then nvl((a.BYTES - NVL(f.BYTES, 0)) / nvl(w.BYTES, 0) * 100, 0) 
else nvl((a.BYTES - NVL(f.BYTES, 0)) / a.BYTES * 100, 0)
end usedTotPct
from DBA_TABLESPACES d,
(select TABLESPACE_NAME, sum(BYTES) bytes
from DBA_DATA_FILES
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME, sum(BYTES) bytes
from DBA_FREE_SPACE
group by TABLESPACE_NAME) f,
(select TABLESPACE_NAME, sum(case when (AUTOEXTENSIBLE='YES') then MAXBYTES else BYTES end) bytes
from DBA_DATA_FILES group by TABLESPACE_NAME) w 
where d.TABLESPACE_NAME = a.TABLESPACE_NAME(+)
and d.TABLESPACE_NAME = f.TABLESPACE_NAME(+)
and d.TABLESPACE_NAME = w.TABLESPACE_NAME(+)
and not (d.EXTENT_MANAGEMENT = 'LOCAL' and d.CONTENTS = 'TEMPORARY')
union all
select
--decode( greatest(90,nvl(t.BYTES/a.BYTES*100,0)), 90, 'TAILLE_TBS_OK', 'WARNING_TBS_FULL' ) STATUT,
case
when d.TABLESPACE_NAME like 'SYS%'                            then '   NO_CHECK'
when 
case
when nvl(w.BYTES /1024/1024, 0) != 0 then nvl(t.BYTES / nvl(w.BYTES, 0) * 100, 0)
else nvl(t.BYTES / a.BYTES * 100, 0)
end > 95
then '!! ALERT_TBS   > 95%'
when
case
when nvl(w.BYTES /1024/1024, 0) != 0 then nvl(t.BYTES / nvl(w.BYTES, 0) * 100, 0)
else nvl(t.BYTES / a.BYTES * 100, 0)
end > 85
then '!  WARNING_TBS > 85%'
else '   TBS_SIZE_OK'
end STATUT,
d.TABLESPACE_NAME                                   tbsName,
decode (d.CONTENTS, 'PERMANENT', 'P',
'TEMPORARY', 'T',
'UNDO', 'U')               prmType,
decode (d.EXTENT_MANAGEMENT, 'LOCAL', 'L', 'D')     extMan,
decode (d.STATUS, 'ONLINE', ' Y', ' N')             tbsStat,
nvl(a.BYTES / 1024 / 1024, 0)                       tbsMsize,
nvl(w.BYTES /1024/1024, 0)                       tbsMsizeMax,
nvl(t.BYTES, 0)/1024/1024                           usedSize,
nvl(t.BYTES / a.BYTES * 100, 0)                     usedPct,
case
when nvl(w.BYTES /1024/1024, 0) != 0 then nvl(t.BYTES / nvl(w.BYTES, 0) * 100, 0)
else nvl(t.BYTES / a.BYTES * 100, 0)
end usedTotPct
from DBA_TABLESPACES d,
(select TABLESPACE_NAME, sum(BYTES) bytes
from DBA_TEMP_FILES
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME, sum(BYTES_CACHED) bytes
from V$TEMP_EXTENT_POOL
group by TABLESPACE_NAME) t,
(select TABLESPACE_NAME, sum(case when (AUTOEXTENSIBLE='YES') then MAXBYTES else BYTES end) bytes
from DBA_DATA_FILES group by TABLESPACE_NAME) w 
where d.TABLESPACE_NAME = a.TABLESPACE_NAME(+)
and d.TABLESPACE_NAME = t.TABLESPACE_NAME(+)
and d.TABLESPACE_NAME = w.TABLESPACE_NAME(+)
and d.EXTENT_MANAGEMENT = 'LOCAL'
and d.CONTENTS = 'TEMPORARY'
order by 1 desc, 2 asc;
Ouais, je sais, cette requête est mortelle ;-)