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.

  • OEM can be used at application level. You can define Metric Extensions that can run any script or SQL statement, evaluate its result and send notification based on the defined threshold. – Balazs Papp Sep 19 '17 at 9:53
  • I can discuss this option with my DBA. Do you have any link for same I can explore – Sakshi Sharma Sep 19 '17 at 10:02

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.