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.

simple pager for Apex 3.0

Although i can claim that in the last 25 years i never got a phone call in the middle of the night for job reasons, i like to know as soon as possible when a major problem has occurred, especially if the error condition prevents users from doing their work.

However, if the application users are anonymous visitors of a web site, it can be difficult, if not impossible, to be contacted timely in case of problems that may arise for a variety of reason, including bugs at different levels, service outages, networking problems, unexpected usage patterns and so on.

If you are an Oracle Application Express developer or administrator you know that in the Monitor Activity section there is a useful built-in report displaying a list of unhandled exceptions that have occurred in the specified time frame, set by default to 1 day.

This report covers errors in the Apex domain, so we won't find any entries if the http server is down, that is when a user got a HTTP-404 error, but we will find entries if an Apex user got a broken report caused by an illegal parameter value, when an Apex process failed and the error was not trapped inside the program, including many other database related issues like a constraint violation or an exceeded tablespace quota that prevented the successful termination of a DML statement, always speaking in the context of Apex applications.

Clearly this approach implies an active participation on our side, because we must explicitly go to the Monitor Activity Page and run the report to know the information, and we know how easy is to forget a task when we have a busy agenda.

What I'm going to suggest here is a more passive approach that lets the computer do the work for us, that is in the end why these damned machines were invented in the first place.

The idea is to have a scheduled job that collects the information and sends us simple reports in form of emails at regular intervals.

Moreover, if you are using a mobile service provider integrating email and SMS, you can easily set up an automated paging system as i did.

And now for the real stuff.

As of Oracle Application Express version 3.0, we have finally all the required bits to carry out this task using supported objects, therefore we no longer need to have special privileges granted for accessing internal objects, in other words the most typical and least appreciated phrase in IT's history: "with the next software release you'll able to do that" has been fulfilled eventually.

Keeping in mind that that Oracle Application Express can still run on top of Oracle 9.2, where DBMS_SCHEDULER is not available, but DBMS_JOB is, i wrote a simple package that allows you to monitor the situation at regular intervals.

Of course the usual disclaimers apply, if you run a nuclear plant don't look at me if anything goes wrong with this program.

Note also that if you (or your DBA) revoked the execution privilege on DBMS_JOB package from PUBLIC (or from the schema user you are trying to compile the package into), the package compilation will fail.
If DBMS_JOB is disabled, that is parameter JOB_QUEUE_PROCESSES is set to zero (0), the pager will not be started, informing you via DBMS_OUTPUT that background jobs are disabled.

The procedures communicate messages via DBMS_OUTPUT and, hopefully, are self-explanatory.

I strongly recommend to perform these tasks from within the Apex environment, but you can do the same from sqlplus too, remembering to COMMIT whenever necessary.

The installation is straightforward, just download the SIMPLE_PAGER for Oracle Application Express 3.0 package and compile it in you schema.

Usage notes are provided in the package specification, however, once the packages have been compiled in the schema, the pager can be started within the next minute as follows:

begin
simple_pager.start_pager(
email       => '...',         -- comma separated list of recipients
email_from  => '...',         -- email of the sender
interval    => 4              -- hours between each alert
);
end;

For instance if you prefer to receive the notifications periodically, on the hour, every 2 hours, starting from 12:00:00 (supposing now it's some time between 00:00 and 11:59:59), then you can specify the optional parameter next_alert, as follows:

begin
simple_pager.start_pager(
email       => '...',                    -- comma separated list of recipients
email_from  => '...',                    -- email of sender
interval    => 2,                        -- hours between each alert
next_alert  => trunc(sysdate) + 1/2      -- start at noon
);
end;

The pager can run without problems in a hosted environment, where each different workspace/user can run its own pager without interfering with others.

Hope it helps.

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