Tablespace 195% Full ... must be the metric system

I was recently refreshing a test database with production data (using datapump if anyone cares) and had forgotten to check the tablespace sizes between prod and test. During the load, I obviously ran out of space. A quick check in prod showed I needed 4GB of space. The test tablespace was set to grow to 2GB max. To add space, I performed a simple command to add the space.

ALTER DATABASE DATAFILE '/u01/oracle/data01/blah.dbf' RESIZE 8GB;

I resumed the datapump and went on my merry way. A few minutes later I received an email from Grid Control that the tablespace was 195% full. 195%?!?! That is crazy. Being that it was Friday afternoon and I wanted to get out of the office for the week, I chalked it up to some flaky Enterprise Manager metric gone wrong and moved on.

Upon returning on Monday, I noticed the same alert had not cleared. Time to dive in and research the issue on Metalink. I found some good docs on Metalink and found reference to some bugs fixed in 10.2.0.4 but no work-arounds to speak of. What I did find was the source of the metric. It is a view called DBA_TABLESPACE_USAGE_METRICS. I had never heard of this one before (looks to be another 10g-ism, and looks very simple and easy to use compared to past ways of calculating tablespace utilization). A quick query on the test database showed that indeed, there was twice as much used space as there was "free space".

Some further digging I discovered that the TABLESPACE_SIZE field includes the max autoextensible size of the tablespace. In this case, I never altered the max size (It was still 2GB), however 4GB was used. The view was indeed showing 195% utilization. One simple alter later to turn the autoextend off and the view nows shows the correct utilization of the tablespace and the EM alert has cleared.

What I'm not entirely sure of at this point is if this is working as designed or a bug. I would expect the autoextensible size to be ignored if the actual size of the tablespace were larger. I'll have to check what happens in a 10.2.0.4 database.

For a good explanation of this somewhat undocumented view, check out Mr Ed's blog and his writing on the DBA_TABLESPACE_USAGE_METRICS view.

I thought I would add a quick example of the issue seen to better illustrate.

SQL> create tablespace test datafile '/oracle/data01/db960/test_01.dbf' size 8M
 autoextend on next 8M maxsize 128M;

Tablespace created.

SQL> create table test_table (test_column number(1)) tablespace test storage(initial 128M);

Table created.

SQL> select tablespace_name,used_space*8192/1024/1024 used_kb ,
 tablespace_size*8192/1024/1024 size_kb,used_percent from dba_tablespace_usage_metrics
 where tablespace_name = 'TEST';

TABLESPACE_NAME                    USED_KB     SIZE_KB USED_PERCENT
------------------------------ ----------- ----------- ------------
TEST                                     0         128            0

SQL> select tablespace_name,used_space*8192/1024/1024 used_kb
     ,tablespace_size*8192/1024/1024 size_kb,used_percent
    from dba_tablespace_usage_metrics where tablespace_name = 'TEST';

TABLESPACE_NAME                    USED_KB     SIZE_KB USED_PERCENT
------------------------------ ----------- ----------- ------------
TEST                                   128         128          100

SQL> select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
           127.9375

SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
                256

As you can see, the autoextensibility causes the DBA_TABLESPACE_USAGE_METRICS tablespace to report bad results.