Monitoring free space in Apex

Although in the Monitor Activity section of Apex you can find a report called schema tablespace utilization, sometimes i find it a bit misleading because it doesn't take into account the real quota assigned to a certain schema on a given tablespace.

For instance, in my case, i have an hosted account at Shellprompt.net, and this report is telling me that i have now 43Mb left on my "primary" tablespace, whereas the difference between my quota and the actual amount of allocated space is 73Mb, in other words, there are another 30Mb of additional space that are not considered in the schema utilization report.

So, in order to have all these calculations readily available, i created the following query, that you can easily turn into a view, if you like.

Notice: Use at your own risk!

When i ran this query on apex.oracle.com within SQL Workshop, my browser got locked up, probably because the server was under a heavy load and it never returned a result (or i gave up before it did...), so this query is meant to be run either in batch mode or during off peak hours.

I am warning you of this possibility because i don't want that you come back and complain that you've lost all your unsaved work in another browser session.

select
a.tablespace_name as "TS Name",
decode(max(c.priv), 1, 'unlimited priv',
decode(max(a.max_blocks), -1, 'unlimited quota',
to_char(round(sum(a.max_bytes)/1024/1024)||'Mb')))
as "Total Quota Mb",
to_char(round(sum(a.bytes)/1024/1024)||'Mb') as "Used Quota Mb",
decode(max(c.priv), 1, 'unlimited priv',
decode(max(a.max_blocks), -1, 'unlimited quota',
to_char(round(sum(a.max_bytes-a.bytes)/1024/1024)||'Mb')))
as "Max Free Space Mb",
to_char(round(sum(b.bytes)/1024/1024)||'Mb') as "Current Free Space Mb"
from user_ts_quotas a,
(select tablespace_name, sum(bytes) as bytes
from user_free_space
group by tablespace_name) b,
(select count(*) as priv
from user_sys_privs
where privilege = 'UNLIMITED TABLESPACE') c
where a.max_blocks != 0
and a.tablespace_name = b.tablespace_name
group by a.tablespace_name;

The difference between the built-in report and this new query is simply explained:

TS Name	Total Quota Mb	Used Quota Mb	Max Free Space Mb	Current Free Space Mb
YOCOYA	200Mb	127Mb	73Mb	43Mb

Total quota is the maximum allowance that has been granted on the tablespace and it can return "unlimited quota" or "unlimited priv" in case you have been granted unlimited quota on the tablespace or the unlimited tablespace system privilege, respectively.
Used quota is the sum of the allocated space taken by your objects.
Max Free Space is simply (Total Quota - Used Quota).
Current Free Space is equivalent to the number returned by the tablespace utilization report.

Note that if the datafile(s) pointed to by your tablespace are not set to autoextend on and the tablespace size is lower than your quota, then the theoretical maximum free space calculated in the last column of this query might not be entirely available unless the DBA manually enlarges the datafile(s). And even in that case, there must enough free disk space for the datafile to autoextend!

The purpose of this query is also to give you some visibility on your actual quota, a key parameter of any hosting contract and that is not displayed by Apex unless you run a query on view USER_TS_QUOTAS (see my previous posting on this topic).

I included this query in the latest version (1.01) of my basic Oracle Application Express monitoring package, called Apex simple pager that i made available for download a couple of weeks ago.

See more articles about Oracle Application Express or download tools and utilities.