Search code examples
sqloracle-databasetriggerspackagejobs

Broken jobs, triggers or packages alert

Sometimes, once or twice a year, I find broken jobs, triggers or packages in my oracle database, to fix it is not a problem but the headache it causes grow exponentially each minute it is broken.

My question is simple, is that a way to have an alert every time a job, trigger or packages alert is broken?

Anything from a email (best solution) to some data written in a table.

Thanks in advance

Solution

  • Depends somewhat on what you mean exactly by "broken", but if you mean that the code has been invalidated:

    SELECT object_type, object_name FROM user_objects WHERE status = 'INVALID'

    or use dba_objects if you have access to it and want to check all schemas at once.