Skip to Main Content
  • Questions

  • NVALID OBJECTS COUNT RANDOMLY INCREASING AND DECREASING

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 23, 2008 - 9:45 am UTC

Last updated: June 24, 2008 - 5:45 pm UTC

Version: 10.2.0.3

Viewed 1000+ times

You Asked

Hi Tom,
We have been noticing that the invalid objects count in our EBS database goes up and down randomly. When urltp.sql is run, the count goes down to 47. But after sometime, the count goes back up to (say) 95 and back down to (say) 75.

When we run utlrp.sql the last_ddl_time in dba_objects is updated to the compilation time. This is causing issues with the auditors as a bulk number of objects have the same last_ddl_time even though no new object was moved or no upgrade done.
Is this normal? Is there some note that documents this behavior?


1) Is there something that causes the invalid objects count to change even though no new object is moved or no object is manually compiled?
2)I understand that Oracle tries to compile an invalid object when it is first accessed. But is the last_ddl_time updated when this happens?

Thanks,
KC

and Tom said...

The number of invalid objects does not 'randomly' increase or decrease.

Objects go invalid for a number of reasons. Grants for example (change grants, things go invalid). Cascading invalidations - procedure P goes invalid, so goes everything that references P.

utlrp issues an alter thing COMPILE, it explicitly compiles the code (that is what it is designed to do). I would suggest...... Just leaving them be. They will recompile all by themselves as needed, you need take no action whatsoever. And the last ddl time will not be touched, implicit compiles do not touch it.


ops$tkyte%ORA10GR2> create user a identified by a;

User created.

ops$tkyte%ORA10GR2> grant create session, create procedure to a;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> grant select on scott.emp to a;

Grant succeeded.

ops$tkyte%ORA10GR2> grant select on scott.emp to public;

Grant succeeded.

ops$tkyte%ORA10GR2> grant select any table to a;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> create or replace procedure p
  2  as
  3  begin
  4          for x in (select * from scott.emp)
  5          loop
  6                  null;
  7          end loop;
  8  end;
  9  /

Procedure created.

a%ORA10GR2>
a%ORA10GR2> column object_name format a8
a%ORA10GR2> select object_name, status,
  2         to_char(last_ddl_time,'dd-mon-yyyy hh24:mi:ss') lddl_time,
  3         to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') now
  4    from user_objects;

OBJECT_N STATUS  LDDL_TIME            NOW
-------- ------- -------------------- --------------------
P        VALID   24-jun-2008 08:07:14 24-jun-2008 08:07:14

a%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> exec dbms_lock.sleep(5)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> revoke select any table from a;

Revoke succeeded.

ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2>
a%ORA10GR2> column object_name format a8
a%ORA10GR2> select object_name, status,
  2         to_char(last_ddl_time,'dd-mon-yyyy hh24:mi:ss') lddl_time,
  3         to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') now
  4    from user_objects;

OBJECT_N STATUS  LDDL_TIME            NOW
-------- ------- -------------------- --------------------
P        INVALID 24-jun-2008 08:07:14 24-jun-2008 08:07:19

<b>Note the revoke invalidated this procedure, we do not remember WHAT GRANT we used when compiling, all we know is "we were able to access scott.emp".  When you revoke select any table from A, that tells us "any of the procedures that access tables might be wrong now, we need to invalidate them"</b>

a%ORA10GR2> exec p

PL/SQL procedure successfully completed.

a%ORA10GR2> select object_name, status,
  2         to_char(last_ddl_time,'dd-mon-yyyy hh24:mi:ss') lddl_time,
  3         to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') now
  4    from user_objects;

OBJECT_N STATUS  LDDL_TIME            NOW
-------- ------- -------------------- --------------------
P        VALID   24-jun-2008 08:07:14 24-jun-2008 08:07:19

<b>and note that the last ddl time did not budge, it compiled itself safely</b>

a%ORA10GR2>
a%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> revoke select on scott.emp from public;

Revoke succeeded.

ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2>
a%ORA10GR2> column object_name format a8
a%ORA10GR2> select object_name, status,
  2         to_char(last_ddl_time,'dd-mon-yyyy hh24:mi:ss') lddl_time,
  3         to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') now
  4    from user_objects;

OBJECT_N STATUS  LDDL_TIME            NOW
-------- ------- -------------------- --------------------
P        INVALID 24-jun-2008 08:07:14 24-jun-2008 08:07:20

<b>again, P went invalid - even though A still has select on emp.  Anything that selected from emp in our database would go invalid if select were revoked from public like that - because anything could have relied on that privilege...</b>

a%ORA10GR2> alter procedure p compile;

Procedure altered.

a%ORA10GR2> select object_name, status,
  2         to_char(last_ddl_time,'dd-mon-yyyy hh24:mi:ss') lddl_time,
  3         to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') now
  4    from user_objects;

OBJECT_N STATUS  LDDL_TIME            NOW
-------- ------- -------------------- --------------------
P        VALID   24-jun-2008 08:07:20 24-jun-2008 08:07:20

<b>when we utlrp (which compiles using alter compile), it will necessarily update the last ddl time, since we explicitly did ddl on it!</b>

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Maybe they're mviews

Greg, June 24, 2008 - 4:32 pm UTC

We have a lot of materialized views that get stale and then become considered invalid.

When I run this, I see that I have 36 mvs listed as invalid.

select count (*) invalids, object_type
from dba_objects
where status = 'INVALID'
group by object_type

When I run this, I see that 64 are stale, but 36 need compile, they are the ones that show as invalid.

select mview_name, staleness, refresh_method, rewrite_enabled, compile_state, master_link,
(select status from dba_objects
where object_name = mview_name
and object_type = 'MATERIALIZED VIEW') staus
from dba_mviews
where staleness in ('STALE', 'NEEDS_COMPILE')
order by 1

They will get fixed next time they refresh, but become stale as the data that they are based on change. They also get fixed by utlrp. Some of our refreshes run in dba_jobs, others run in the concurrent manager. The annoying thing about utlrp is that it updates the timestamp in dba_registry.

You can schedule periodic recompiles, or just exclude mviews from the invalid object list. Some people spend too much time worrying about invalids...

Greg
Tom Kyte
June 24, 2008 - 5:45 pm UTC

I would just stop utlrp altogether what is the point or premise of it? why bother???

I never worry about invalids, they take care of themselves OR someone/something complains (that should hardly ever happen and only after you make some big mistake of a change)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library