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 ;-)
