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 errors or 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.

  • What do you mean by "compile a view"? And what are the symptoms of "failing to compile"?
    – mustaccio
    Commented Oct 13, 2019 at 19:25
  • @mustaccio As a novice, I thought that compile meant to select from a view. But it sounds like that was wrong. I'll edit the question.
    – User1974
    Commented Oct 13, 2019 at 19:39
  • In computer science, and IT in general, "to compile" commonly means "to convert a program from a human readable source form into machine-executable code". And it still isn't clear what is meant by "views have errors". Can you give an example of such an error?
    – mustaccio
    Commented Oct 13, 2019 at 19:46
  • @mustaccio Thanks for your patience. I provided some examples in the question.
    – User1974
    Commented Oct 13, 2019 at 20:25
  • It is not a matter of GIS world if users change table/view structures. It only depends on internal processes and privileges. It is really strange if people changes their tables but you can't create primary keys, triggers, unique indexes, etc - this does not match. Commented Oct 14, 2019 at 6:14

1 Answer 1


You can get invalid views with a procedure like this:


    CURSOR InvaliViews AS
    WHERE object_type = 'VIEW'
        AND status = 'INVALID';


    FOR aView IN InvaliViews LOOP
            sender => 'noreply@your_domain.com',
            recipients => 'your-email@your_domain.com',
            subject => 'Notification about invalid views',
            message => 'View '||aView.OBJECT_NAME||' is not valid');
    end loop;


You can run such procedure once a week triggered by Scheduler Job

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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