Yes, I know: many scripts are available to get tablespaces usage from your Oracle database.
The one I’m going to give you is not magic, but it can handle some special situations and it can be easily integrate into a “custom metric” in Oracle Enterprise Manager.
The script runs on Oracle 10g and onward.
set linesize 100
set pagesize 100
col file_name for a60
SELECT
a.tablespace_name "TABLESPACE_NAME",
round(a.current_mb) "CURRENT_SIZE_MB",
round(a.max_mb) "MAX_SIZE_MB",
round(a.current_mb - c.free) "USED_MB",
round(a.max_mb - (a.current_mb - c.free)) "FREE_MB",
round(((a.current_mb - c.free)*100)/a.max_mb) "USED_PCT"
FROM
(
SELECT
tablespace_name,
SUM(a.bytes)/(1024*1024) current_mb,
SUM(decode(a.autoextensible, 'NO', a.bytes/(1024*1024), GREATEST (a.maxbytes/(1024*1024),a.bytes/(1024*1024)))) max_mb
FROM
dba_data_files a
GROUP BY tablespace_name
) a,
(
SELECT
d.tablespace_name, sum(nvl(c.bytes/(1024*1024),0)) free
FROM
dba_tablespaces d,
dba_free_space c
WHERE
d.tablespace_name = c.tablespace_name(+)
--AND d.contents='PERMANENT'
--AND d.status='ONLINE'
GROUP BY
d.tablespace_name
) c
WHERE
a.tablespace_name = c.tablespace_name
ORDER BY 6;
The output looks like:
TABLESPACE_NAME CURRENT_SIZE_MB MAX_SIZE_MB USED_MB FREE_MB USED_PCT ------------------------------ --------------- ----------- ---------- ---------- ---------- TOOLS 16 512 2 510 0 XDB_DAT1 16 64 1 63 2 UNDO01 5120 2048 136 1912 3 SYSAUX 3328 16384 3095 13289 19 SYSTEM 1152 4096 830 3266 20 MGMT_TABLESPACE 32767,98 32768 32692 76 100
It will give you the following informations:
- CURRENT_SIZE_MB: current size of the tablespace in MB
- MAX_SIZE_MB: max size of all datafiles (in MB) if autoextend is in use
- USED_MB: amount of MB used in all datafiles
- FREE_MB: amount of free MB in all datafile
- USED_PCT: percent of space used
It takes into account:
- Mix between auto-extensible and not auto-extensible datafiles
- Tablespaces with different block sizes
- Datafiles with a size (BYTES) greater than maxsize (MAXBYTES)
The SQL script does not return TEMP tablespaces usage.
If you want to exclude UNDO tablespaces, you have to uncomment the line “AND d.contents=’PERMANENT'”.
If you want to exclude OFFLINE tablespaces, you have to uncomment the line “AND d.status=’ONLINE'”.
Stay tuned for more DBA stuff!
Bonjour monsieur,
serait-il possible de rajouter une petite colonne avec des *** en fonction du taux de remplissage ?
Merci d’avance,