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
oturan23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidSenior Oracle Database AdministratorCommented:
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.
0
johnsoneSenior Oracle DBACommented:
Errors can also be viewed in the DBA_ERRORS and ALL_ERRORS views.  That should tell you why it is invalid after compiling.
0
oturan23Author Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DavidSenior Oracle Database AdministratorCommented:
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/utlrp.sql.  The description is in the file.
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;
0
johnsoneSenior Oracle DBACommented:
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.
0
oturan23Author Commented:
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.
0
DavidSenior Oracle Database AdministratorCommented:
ALTER MATERIALIZED VIEW XXBI_STOK_MV COMPILE;
ALTER MATERIALIZED VIEW
  [ schema. ](materialized_view)
  [ physical_attributes_clause
  | table_compression
  | LOB_storage_clause
    [, LOB_storage_clause ]...
  | modify_LOB_storage_clause
    [, modify_LOB_storage_clause ]...
  | alter_table_partitioning
  | parallel_clause
  | logging_clause
  | allocate_extent_clause
  | shrink_clause
  | { CACHE | NOCACHE }
  ]
  [ alter_iot_clauses ]
  [ USING INDEX physical_attributes_clause ]
  [ MODIFY scoped_table_ref_constraint
  | alter_mv_refresh
  ]
  [ { ENABLE | DISABLE } QUERY REWRITE
  | COMPILE
  | CONSIDER FRESH
  ] ;
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
oturan23Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.