Free space and dba_tablespace_usage_metrics

You logon to your database to check the free space and used percent of the tablespaces and notice one or more of them with over 90% used space. But there is no alert in Enterprise Manager and no thresholds were exceeded.
That’s because most of the SQL scripts used or found on Google are based solely on current datafile size and database free space.
A solution for this is called dba_tablespace_usage_metrics view.

Let’s make a test:

First we create a tablespace with a 50m datafile:

SQL> create tablespace ALEX datafile '/data/alex1.dbf' size 50m;

Tablespace created.

Now let’s check the free space and used percent.
PERCM – Percent of used space from dba_tablespace_usage_metrics
PERC – << Classic approach >> Percent of used space calculated from dba_data_files and dba_free_space
Notice that the two values are exactly the same at this point:

SQL> SELECT m.tablespace_name,
    round(max(m.used_percent),1) PERCM,
	round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-NVL(sum(f.bytes),0)/count(distinct d.file_id))*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1) PERC,
	round(max(m.tablespace_size*t.block_size/1024/1024),1) TOTALM,
	round(max(m.used_space*t.block_size/1024/1024),1) USED,
	round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREEM
FROM  dba_tablespace_usage_metrics m, dba_tablespaces t, dba_data_files d, dba_free_space f
WHERE m.tablespace_name=t.tablespace_name
AND d.tablespace_name=t.tablespace_name
AND d.tablespace_name=f.tablespace_name
GROUP BY m.tablespace_name;

TABLESPACE_NAME     PERCM      PERC    TOTALM      USED     FREEM
--------------- --------- --------- --------- --------- ---------
ALEX                    2         2        50         1        49
EXAMPLE               2.6      89.6   12105.4     309.8   11795.6
SYSAUX                4.3        95   12319.8     532.2   11787.6
SYSTEM                5.7      99.8   12469.8     708.8     11761
UNDOTBS1                0       2.5   11809.8       1.3   11808.6
USERS                88.1      93.8        33      29.1       3.9

Next we set the datafile to autoextend to a maximum of 10 Gigabytes and run the select again:

SQL> alter database datafile '/data/alex1.dbf' autoextend on maxsize 10g;

Database altered.

TABLESPACE_NAME     PERCM      PERC    TOTALM      USED     FREEM
--------------- --------- --------- --------- --------- ---------
ALEX                   .1         2       932         1       931
EXAMPLE               2.6      89.6   12105.4     309.8   11795.5
SYSAUX                4.3        95   12319.7     532.2   11787.5
SYSTEM                5.7      99.8   12469.7     708.8     11761
UNDOTBS1                0       2.5   11809.8       1.3   11808.6
USERS                88.1      93.8        33      29.1       3.9

The PERCM is totally different now. And TOTALM as well.
Lets check the disk space on the volume:

[oracle@rac2 ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       26G   13G   12G  52% /
/dev/sda1              99M   23M   71M  25% /boot
tmpfs                1002M  256M  746M  26% /dev/shm
/dev/sdc             1017M   84M  882M   9% /data

If we do the math:
882M(free on volume) + 50M(current datafile size) = 932M – 1M(used on datafile) = 931M
This means that the free space estimated in dba_tablespace_usage_metrics view is based on the autoextend of datafiles and also the free space left on the volume where they are located.
In this case its 931M not the 10 Gigabytes we’ve set the maxsize to.

Also a good thing to know is that if there are more tablespaces on the same volume, they all estimate the same amount of free space:

TABLESPACE_NAME      TOTAL       USED    MB_FREE    PERCENT
--------------- ---------- ---------- ---------- ----------
TEST                   882          1        881         .1
ALEX                   882          1        881         .1

To check the Thresholds for your tablespaces you can use: dba_tablespace_thresholds.

A select combining all of these views:
11gR2 and above:

SELECT m.tablespace_name,
    round(max(m.used_percent),1) PERCM,
	round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1) PERC,
	round(max(m.tablespace_size*t.block_size/1024/1024),1) TOTALM,
	round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1) TOTAL,
	round(max(m.used_space*t.block_size/1024/1024),1) USED,
	round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREEM,
	round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,		
	count(distinct d.file_id) DBF_NO,
	max(to_number(tt.warning_value)) WARN,
	max(to_number(tt.critical_value)) CRIT,
	max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM  dba_tablespace_usage_metrics m, dba_tablespaces t, dba_data_files d, dba_tablespace_thresholds tt
WHERE m.tablespace_name=t.tablespace_name
AND d.tablespace_name=t.tablespace_name
AND tt.tablespace_name=d.tablespace_name
AND tt.metrics_name='Tablespace Space Usage'
GROUP BY m.tablespace_name
order by 2 desc;

11gR1 and below:

SELECT m.tablespace_name,
    round(max(m.used_percent),1) PERCM,
    round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1) PERC,
    round(max(m.tablespace_size*t.block_size/1024/1024),1) TOTALM,
    round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1) TOTAL,
    round(max(m.used_space*t.block_size/1024/1024),1) USED,
    round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREEM,
    round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,    
    count(distinct d.file_id) DBF_NO,
    max(to_number(tt.warning_value)) WARN,
    max(to_number(tt.critical_value)) CRIT,
    max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM  dba_tablespace_usage_metrics m, dba_tablespaces t, dba_data_files d, dba_thresholds tt
WHERE m.tablespace_name=t.tablespace_name
AND d.tablespace_name=t.tablespace_name
and tt.metrics_name='Tablespace Space Usage'
and tt.object_name is null
GROUP BY m.tablespace_name
order by 2 desc;

Sample output:

TABLESPACE_NAME      PERCM       PERC     TOTALM      TOTAL       USED      FREEM       FREE     DBF_NO       WARN       CRIT OK?
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
USERS                 88.1       93.8         33         31       29.1        3.9        1.9          1         80         85 NO!
SYSTEM                 5.7       99.8    12469.8        710      708.8    11761.1        1.3          1         80         85 OK
SYSAUX                 4.3         95    12319.8        560      532.2    11787.6       27.8          1         80         85 OK
EXAMPLE                2.6       89.6    12105.4      345.6      309.8    11795.6       35.8          1         80         85 OK
ALEX                    .1          2      931.9         50          1      930.9         49          1         81         90 OK
UNDOTBS1                 0        2.5    11809.8         50        1.3    11808.6       48.8          1          0          0 NO!

CAUTION!
Be very careful when resizing your datafiles!
If they are set to autoextend then set the maxsize >= size of the datafile after resize!!

You’ve extended the tablespace and the alert is still on the frontpage of EM?
You can force the metric collection like this:

emctl control agent runCollection orcl:oracle_database problemTbsp_10i_Loc
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
—————————————————————
EMD runCollection completed successfully

To find out the metric collection names and also to check the schedule for the upcoming metric collections you can use:
emctl status agent scheduler

To modify the metric collection schedule on 11g:
/u01/app/oracle/product/11.2.0/db_1/sysman/admin/default_collection/database.xmlp

12g:
Oracle Database>Monitoring>All Metrics>Tablespaces Full>Modify
2013-10-22_00_24_26-All_Metrics__ora1_(Database_Instance)_-_Oracle_Enterprise_Manager[1]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s