Monitoring Tablespace Usage in Oracle

Thursday Jun 11th 2015 by David Fitzjarrell

Monitoring tablespace space is a constant, yet necessary, task.  It's changed over the years and releases, so read on to see how it used to be done and what new views are available to make the task easier.

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:

FROM dba_tablespaces t,
    SELECT tablespace_name,
        SUM(NVL(bytes,0))/(1024*1024*1024) total_gb
    FROM dba_data_files
    GROUP BY tablespace_name) f,
    SELECT tablespace_name,
        SUM(NVL(bytes,0))/(1024*1024*1024) used_gb
    FROM dba_segments
    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:

 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 TABLESPACE_NAME                                                                   VARCHAR2(30)
 USED_SPACE                                                                        NUMBER
 TABLESPACE_SIZE                                                                   NUMBER
 USED_PERCENT                                                                      NUMBER


Querying the view:


------------------------------ ---------- --------------- ------------
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.


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,
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> select value from v$parameter where name = 'db_block_size';

1 row selected.

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,

------------------------------ ----------- ---------- ----------------- ------------- ------------
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.


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.

See all articles by David Fitzjarrell

Mobile Site | Full Site