I often see questions in Oracle newsgroups and forums about monitoring space in tablespaces, and many of those questions are regarding Enterprise Manager 12c and the alerts it generates reporting the percentage of used space. Of course how you monitor free space depends on whether or not the datafiles are autoextensible. Let’s look at several ways to monitor free and used space, starting with methods used in older releases.
In versions prior to Oracle 10 DBA_TABLESPACES, DBA_DATA_FILES and DBA_FREE_SPACE were used to generate such reports. One script, from MOS, uses these three views to compute the used space percentage:
SELECT
SUM(s.used_gb)/SUM(f.total_gb)*100
FROM dba_tablespaces t,
(
SELECT tablespace_name,
SUM(NVL(bytes,0))/(1024*1024*1024) total_gb
FROM dba_data_files
WHERE TABLESPACE_NAME = 'MYTBS'
GROUP BY tablespace_name) f,
(
SELECT tablespace_name,
SUM(NVL(bytes,0))/(1024*1024*1024) used_gb
FROM dba_segments
WHERE TABLESPACE_NAME = 'MYTBS'
GROUP BY tablespace_name) s
WHERE t.tablespace_name = f.tablespace_name (+)
AND t.tablespace_name = s.tablespace_name (+);
It does arrive at a percentage but it may not match the value reported by Enterprise Manager 12c. Also the above query doesn’t take into account any autoextensible data files, which can produce a percent used result that doesn’t accurately reflect the maximum size those datafiles can reach. Modifying the above query slightly produces a more ‘realistic’ result for datafiles set to autoextend:
set linesize 132 tab off trimspool on
set pagesize 105
set pause off
set echo off
set feedb on
column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00
select a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from dba_data_files
group by tablespace_name ) a,
( select tablespace_name, sum(bytes) tot_used
from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
--and (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
and a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and a.tablespace_name not like 'UNDO%'
order by 1
--order by 5
/
select *
from dba_temp_free_space;
Notice that for data files set to autoextend the script considers the maximum bytes the files can contain rather than the existing bytes currently allocated. It provides a better ‘picture’ of the used space since it also considers the total space the file can attain even though the file may not have that space currently allocated. In addition a short report on temporary space follows the main script output.
For releases from 11.2 on, a new view is available that provides a concise report on tablespace usage named DBA_TABLESPACE_USAGE_METRICS:
SQL> desc DBA_TABLESPACE_USAGE_METRICS
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
TABLESPACE_NAME VARCHAR2(30)
USED_SPACE NUMBER
TABLESPACE_SIZE NUMBER
USED_PERCENT NUMBER
SQL>
Querying the view:
SQL> select * From DBA_TABLESPACE_USAGE_METRICS;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
DFLT 54440 4194302 1.29795136
INDX 128 64000 .2
SYSAUX 244056 4194302 5.81875125
SYSTEM 66280 4194302 1.5802391
TEMP 0 4194302 0
UNDOTBS1 224 4194302 .005340579
USERS 20616 4194302 .491523977
7 rows selected.
SQL>
The values reported are in blocks, not bytes, which can be easily computed using the database block size:
column value new_value dbblocksz noprint
select value from v$parameter where name = 'db_block_size';
select tablespace_name,
used_space used_blocks,
(used_space*&dbblocksz)/(1024*1024) used_mb,
tablespace_size tablespace_blocks,
(tablespace_size*&dbblocksz)/(1024*1024) tablespace_mb,
used_percent
from dba_tablespace_usage_metrics;
Looking at the output that query provides, both the blocks and megabytes are displayed:
SQL> column value new_value dbblocksz noprint
SQL>
SQL> select value from v$parameter where name = 'db_block_size';
1 row selected.
SQL>
SQL> select tablespace_name,
2 used_space used_blocks,
3 (used_space*&dbblocksz)/(1024*1024) used_mb,
4 tablespace_size tablespace_blocks,
5 (tablespace_size*&dbblocksz)/(1024*1024) tablespace_mb,
6 used_percent
7 from dba_tablespace_usage_metrics;
old 3: (used_space*&dbblocksz)/(1024*1024) used_mb,
new 3: (used_space*8192)/(1024*1024) used_mb,
old 5: (tablespace_size*&dbblocksz)/(1024*1024) tablespace_mb,
new 5: (tablespace_size*8192)/(1024*1024) tablespace_mb,
TABLESPACE_NAME USED_BLOCKS USED_MB TABLESPACE_BLOCKS TABLESPACE_MB USED_PERCENT
------------------------------ ----------- ---------- ----------------- ------------- ------------
DFLT 54440 425.3125 4194302 32767.9844 1.29795136
INDX 128 1 64000 500 .2
SYSAUX 244056 1906.6875 4194302 32767.9844 5.81875125
SYSTEM 66280 517.8125 4194302 32767.9844 1.5802391
TEMP 0 0 4194302 32767.9844 0
UNDOTBS1 688 5.375 4194302 32767.9844 .016403206
USERS 20616 161.0625 4194302 32767.9844 .491523977
7 rows selected.
SQL>
The script uses SQL*Plus functionality to preserve the value of db_block_size for the current database so it can be used througout the main query to compute the megabytes using the reported size in blocks. This avoids hard-coding a block size, allowing the script to produce correct results for any database where it is run. It should be noted that the TABLESPACE_BLOCKS column reports the maxblocks for autoextensible data files, otherwise it reports the currently allocated blocks thus USED_PERCENT reflects the total space used in reference to the maximum allocatable file size. This prevents pre-mature warnings of available free space.
It used to be that the DBA needed to write complex scripts to report on free and used space in tablespaces, which could be incorrect if autoextensible data files were in use. The DBA_TABLESPACE_USAGE_METRICS view can simplify that task in Oracle versions 11.2 and later, as shown above, so consider using it if it’s available to monitor tablespace usage.