Query for Tablespace usage with Autoextend

                                         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.

##Query for Tablespace usage with Autoextend ##

set linesize 100
set pagesize 100

select
                a.tablespace_name,
                round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
                round(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), 
                b.maxextend*8192/(1024*1024*1024)))) MAX_GB,
                (SUM(a.bytes)/(1024*1024*1024) - round(c.Free/1024/1024/1024)) USED_GB,
                round((SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), 
                b.maxextend*8192/(1024*1024*1024))) - (SUM(a.bytes)/(1024*1024*1024) - 
                round(c.Free/1024/1024/1024))),2) FREE_GB,
                round(100*(SUM(a.bytes)/(1024*1024*1024) - 
                round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), 
                b.maxextend*8192/(1024*1024*1024))))) USED_PCT
from
                dba_data_files a,
                sys.filext$ b,
                (SELECT
                               d.tablespace_name ,sum(nvl(c.bytes,0)) Free
                FROM
                               dba_tablespaces d,
                               DBA_FREE_SPACE c
                WHERE
                               d.tablespace_name = c.tablespace_name(+)
                               group by d.tablespace_name) c
WHERE
                a.file_id = b.file#(+)
                and a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name, c.Free/1024
ORDER BY tablespace_name;