Home » RDBMS Server » Server Administration » Objects Invalid due to auto-compilation (Oracle 10.2.0.4)
Objects Invalid due to auto-compilation [message #558878] Wed, 27 June 2012 01:44 Go to next message
orapratap
Messages: 113
Registered: November 2011
Location: Canada
Senior Member
Hello

One of my friends is facing a peculiar problem where objects are getting "Invalid" during execution
I suspect it is happening as they are changing system date during their testing (time travel) which can create conflicted last_ddl_time on objects having dependencies
Consider a scenario
[1]
system date is 10-06-2012
there are total 10 objects which has status as 'valid'
[2]
the system date is changed to 10-07-2012
Now out of 10 Only 5 objects are compiled
During execution ORA-04065,ORA-06508, ORA-06512 are observed
[3]
the system date is brought back to 10-06-2012
Again during execution ORA-04065,ORA-06508, ORA-06512 are observed

suppose in step 2 objects are compiled whereas there synonyms are compiled in step 1, only
thus last_ddl_time for objects will be later to that of its' synonym...ambiguous

Does database validate last_ddl_time for objects having dependency during execution and then auto-compiles or invalidates the objects?

Regards
Orapratap
Re: Objects Invalid due to auto-compilation [message #558887 is a reply to message #558878] Wed, 27 June 2012 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instance invalidates objects on modification of depending objects so it does not need last_ddl_time on execution.
However time is important for remote objects.

Copy and paste your session, the WHOLE session including objects definition.

Regards
Michel
Re: Objects Invalid due to auto-compilation [message #558891 is a reply to message #558887] Wed, 27 June 2012 02:34 Go to previous messageGo to next message
orapratap
Messages: 113
Registered: November 2011
Location: Canada
Senior Member
Hello Michel

Thanks for the quick reply

You said
Quote:
Instance invalidates objects on modification of depending objects so it does not need last_ddl_time on execution


I agree. But can we say that as far as there aren't any remote objects, last_ddl_time does not matter (or Not validated) for the objects having dependency if their status is "valid"?

you said
Quote:
However time is important for remote objects


I agree. I believe in this case we set "remote_dependencies_mode=signature" to explicitly make the system overlook last_ddl_time of the objects

Unfortunately at this moment I do not have the exact session details but I shall ask and post it here

Thanks and Regards
Orapratap

Re: Objects Invalid due to auto-compilation [message #558901 is a reply to message #558891] Wed, 27 June 2012 03:09 Go to previous message
Michel Cadot
Messages: 59806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I agree. But can we say that as far as there aren't any remote objects, last_ddl_time does not matter (or Not validated) for the objects having dependency if their status is "valid"?


Yes.

Quote:
I agree. I believe in this case we set "remote_dependencies_mode=signature" to explicitly make the system overlook last_ddl_time of the objects


Yes.

Quote:
Unfortunately at this moment I do not have the exact session details but I shall ask and post it here


OK, I hold on...

Regards
Michel
Previous Topic: Reg database migration
Next Topic: restorin the oracle services in windows 2008 server
Goto Forum:
  


Current Time: Thu Nov 27 06:33:07 CST 2014

Total time taken to generate the page: 0.10819 seconds