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.