Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Materialized view "Invalid" state problem.

Posted on 2008-11-19
8
Medium Priority
?
2,714 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:oturan23
  • 3
  • 3
  • 2
8 Comments
 
LVL 23

Expert Comment

by:David
ID: 22994480
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
 
LVL 35

Expert Comment

by:johnsone
ID: 22994697
Errors can also be viewed in the DBA_ERRORS and ALL_ERRORS views.  That should tell you why it is invalid after compiling.
0
 

Author Comment

by:oturan23
ID: 23003468
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 23

Expert Comment

by:David
ID: 23003712
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
 
LVL 35

Expert Comment

by:johnsone
ID: 23004031
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
 

Author Comment

by:oturan23
ID: 23004401
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
 
LVL 23

Accepted Solution

by:
David earned 2000 total points
ID: 23005042
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
 

Author Comment

by:oturan23
ID: 23010860
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question