I had never experience anything like this but one of the scripts containing
select count(*) from user_tables where table_name = 'XXX';
started to throw
PL/SQL: ORA-04063: view “SYS.USER_TABLES” has errors
I use visual tool to look into it – and here it is
And I see many views are invalidated. What is the possible cause for this? I’ve never seen
What I did is ran this to create
select 'alter view SYS.' || object_name || ' compile;' from user_objects where object_type = 'VIEW' and status = 'INVALID';
Created 707 rows. Ran them, still 202 remains problematic. Run again, still 202.
And here is error sample
SQL Error: ORA-00604: error occurred at recursive SQL level 1 ORA-04063: view "SYS.DBA_OBJECT_TABLES" has errors 00604. 00000 - "error occurred at recursive SQL level %s" *Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables). *Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.