Alert on Table size

Question

I need to create an alert on my application table when it reaches 98% of its threshold. I wrote an SP which checks the rows and logs the information into a log file which was monitored by alert system and that raised alarms successfully.

But now my DBA team is saying they cannot grant the application DB user read/write privileges on UTL_FILE in production. And OEM (Oracle Enterprise Manager) can't be used at application level.

• What are my options?
• How can I get an alert on table size?

Reference: UTL_FILE

With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.

Answer 1

You have not explained, how you start your SP. If done via db-jobs, then you might have this problem.

But you can also start a request via any client-tool. Simply by using sqlplus or any other client, connecting the db and returning the threshold to any location you like.

Similar way (client-access) is made by many monitoring tools. You can create a separate account with very limited and restricted access. You can even de-couple the read access by a special higher privileged SP, that returns just the threshold without having SELECT privilege on monitored table itself.

Answer 2

I need to create an alert on my application table when it reaches 98% of its threshold

A single table really should not require this kind of micro-management.

An entire Tablespace sure, but just one table?

I wrote an SP which checks the rows and logs the information into a log file which was monitored by alert system and that raised alarms successfully.

So you have a stored procedure that wants to write to the file system on the database server that then gets picked up by your Monitoring (or, more likely, Log Aggregating) application, just to tell you how many rows are in a table? Sounds a tad over-complicated to me.

Why not write a query to count the rows and have your monitoring system execute that query through sqlplus periodically? OK, the output will need a little "massaging" to trim out sqlplus's "fluff", but that's hardly difficult these days.

I've used exactly this technique to interface with real-time monitoring software such as CheckMK.