Materialized view "Invalid" state problem.
Hi;
In the Enterprise Manager Console home page, often the alert arises "N objects are invalid in XXX schema".
When i go to that objects, they are materialized views and it says "Need recompile" . Most of the "invalid state" is disappears after recompile. However one of them is always invalid. After sometimes others are becoming invalid again. Strange thing is that these materialized views are working fine and the refresh times are correct. I mean there is no refreshing problem. Do you have any idea why the enterprise manager calls them invalid?
thanks
ozgur
alert.jpg
invalid-objects.jpg
In the Enterprise Manager Console home page, often the alert arises "N objects are invalid in XXX schema".
When i go to that objects, they are materialized views and it says "Need recompile" . Most of the "invalid state" is disappears after recompile. However one of them is always invalid. After sometimes others are becoming invalid again. Strange thing is that these materialized views are working fine and the refresh times are correct. I mean there is no refreshing problem. Do you have any idea why the enterprise manager calls them invalid?
thanks
ozgur
alert.jpg
invalid-objects.jpg
David VanZandt
When you recompile from the CLI, there's opportunity to follow up with "SHOW ERRORS", which (obviously) returns the offending SQL statement and error code. In my experience this type symptom suggests that an underlying table, view, or even index has become invalid. For example, the object priviledges by a role on a view may have been changed.
Errors can also be viewed in the DBA_ERRORS and ALL_ERRORS views. That should tell you why it is invalid after compiling.
ASKER
johnsone: --> i can't find any error in those views.
dvz: -->" When you recompile from the CLI"
I don't understand what you mean, sorry i don't have too much experience.
dvz: -->" When you recompile from the CLI"
I don't understand what you mean, sorry i don't have too much experience.
CLI is the command line interpreter, or the window you get from running cmd.exe. My bad, I meant the SQL*Plus command prompt.
I should also mention it's useful to try Oracle's script to recompile all invalid objects: ORACLE_HOME/rdbms/admin/ut
I can't speak for johnsone, but I believe the intent is to check the two views immediately after your recompilation fails: SELECT * FROM all_errors;
I should also mention it's useful to try Oracle's script to recompile all invalid objects: ORACLE_HOME/rdbms/admin/ut
I can't speak for johnsone, but I believe the intent is to check the two views immediately after your recompilation fails: SELECT * FROM all_errors;
The error views hold the errors after you compile. You should not need to check them immediately. If you want to use the show errors command, then you need to use that immediately. I was just giving an alternate to the show errors command, which should work at any time. There should be records in those views if you compiled something and the compile failed.
ASKER
Hi; the script compiles all objects in the database and it says;
**** There should be no other DDL on the database while running the
Rem script. Not following this recommendation may lead to deadlocks.
Rem *****
which is impossible to interrupt DDL for our db. I need a command to compile just an object (materialized view here)
Thanks
Ozgur.
**** There should be no other DDL on the database while running the
Rem script. Not following this recommendation may lead to deadlocks.
Rem *****
which is impossible to interrupt DDL for our db. I need a command to compile just an object (materialized view here)
Thanks
Ozgur.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank dvz, this was the command that i looked for. However when i run the command it just said "snapshot altered" and the materialized view is still invalid. i think we really need to run "utlrp.sql" when the system is in maintenance.