Monitoring Tablespace Usage in Oracle

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.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Latest Articles