I have 40 views that are integrated/synced to a work order management system on a weekly basis.
The views are based on GIS tables which are notoriously messy. Over time, the views can end up becoming invalid.
- A user deletes or renames a column in an underlying table, but fails to notify I.T. of the change, so the view is not updated accordingly.
- As a result, the view would become invalid:
ORA-04063: view "ROADS_VW" has errorsor
ORA-00904: "FIELD1": invalid identifier.
I would like to catch & fix invalid views (or fix the underlying data) before the views are synced to the work order management system each week.
Is there a way to get Oracle to notify me about invalid views?
For instance, if the integrations occur on Fridays, then get an email on Thursdays if any of the views are invalid.