Invalid objects are existing after running utlrp.sql script

[Former Member]

Hi,

Please find below log as invalid count is 19 .i have run the utlrp.sql script then count is 0.if i check next day the invalid count shows 19 again.

Please let me know the solution .

Log:

SQL> select name from v$database;

NAME
---------
HPR

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
        19

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2012-10-12 17:08:15

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2012-10-12 17:08:19

PL/SQL procedure successfully completed.

OBJECTS WITH ERRORS
-------------------
                  0

ERRORS DURING RECOMPILATION
---------------------------
                          0

PL/SQL procedure successfully completed.

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0

[volker_borowski2]

Hi,

Most likely views, esp. in BW systems.

If a dependend object of a view faces a DDL operation, the view gets invalidated.

It will be re-compiled automaticly when first re-selected, as long as it will be still sytactically valid.

So select as Stefan described. If you have a view with these objects, do

select * from owner.viewname where rownum=1;

After this recount the number of invalid objects. Should be one less.

Volker

[Former Member]

Hi Stefan,

As you asked,please find below info

SQL> select owner,object_name,object_type from dba_objects where status='INVALID';

OWNER        OBJECT_NAME                                        OBJECT_TYPE
------------ -------------------------------------------------- ------------
PUBLIC       DBA_HIST_FILESTATXS                                SYNONYM
PUBLIC       DBA_HIST_SQLSTAT                                   SYNONYM
PUBLIC       DBA_HIST_SQLBIND                                   SYNONYM
PUBLIC       DBA_HIST_SYSTEM_EVENT                              SYNONYM
PUBLIC       DBA_HIST_WAITSTAT                                  SYNONYM
PUBLIC       DBA_HIST_LATCH                                     SYNONYM
PUBLIC       DBA_HIST_LATCH_MISSES_SUMMARY                      SYNONYM
PUBLIC       DBA_HIST_DB_CACHE_ADVICE                           SYNONYM
PUBLIC       DBA_HIST_ROWCACHE_SUMMARY                          SYNONYM
PUBLIC       DBA_HIST_SGASTAT                                   SYNONYM
PUBLIC       DBA_HIST_SYSSTAT                                   SYNONYM

OWNER        OBJECT_NAME                                        OBJECT_TYPE
------------ -------------------------------------------------- ------------
PUBLIC       DBA_HIST_SYS_TIME_MODEL                            SYNONYM
PUBLIC       DBA_HIST_OSSTAT                                    SYNONYM
PUBLIC       DBA_HIST_PARAMETER                                 SYNONYM
PUBLIC       DBA_HIST_SEG_STAT                                  SYNONYM
PUBLIC       DBA_HIST_ACTIVE_SESS_HISTORY                       SYNONYM
PUBLIC       DBA_HIST_TABLESPACE_STAT                           SYNONYM
PUBLIC       DBA_HIST_SERVICE_STAT                              SYNONYM
PUBLIC       DBA_HIST_SERVICE_WAIT_CLASS                        SYNONYM

19 rows selected.

SQL> select count(*) from dba_objects where status='INVALID'
  2  ;

  COUNT(*)
----------
        19

[stefan_koehler]

Hi Narendra,

which oracle version are you using?

This seems like known bug #4077874 / #3603160 in older database releases like 10.2.0.2. The invalidation is caused by the automatic structure changes (adding / dropping partitions) to the underlying tables. These views are based on the history statistic tables.

There are one off patches available for it, but nothing you should really worry about.

If you just run a query like "SELECT * FROM <VIEW>" - the object will become valid again until the next automatic structure changes.

Regards

Stefan

[Former Member]

database version is 11.2.0.2

[Former Member]

Hi,

maybe invalid objects are because you haven't activated Oracle Diagnostics Package. Look for more information in SAP Note 1028068 - Required Oracle options for the DBA Cockpit and SAP Note 1250596 - Diagnostic package for Oracle systems in the DBA Cockpit.

Regards.

[stefan_koehler]

Hello Narendra,

well that is nothing spectacular so far.

Please run the following query to determine the objects (maybe the invalid objects are only customer defined SAP views for example).

SQL> set linesize 300
SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE from dba_objects where STATUS = 'INVALID';

Regards

Stefan

[former_member182034]

hi Narendra,

would you share the oracle version? and what is  the status of Oracle Generic and Interim patches.

Please go through the README file of SBP which you applied on your system because all things are mentioned in this file.

Regards,