Home » SQL & PL/SQL » SQL & PL/SQL » Query on invalid objects (Oracle 11g,Linux 5)
Query on invalid objects [message #589688] Wed, 10 July 2013 01:18 Go to next message
suhasdba
Messages: 141
Registered: April 2013
Location: INDIA
Senior Member
Hi,

Quote:
by default the job runs once every 24 hours.
When you see LAST_ANALYZED being days, weeks, or months in the past do not be alarmed.
If/when the data in a table does not change, then the statistics do not need to change.
Oracle collects new statistics when enough of the data (about 10%) has changed.


Like above statement for DBMS_STATS job, is there a automatic job that runs every day to compile invalid objects
for schema or we need to compile it manually ?

Thanks,
Suhas
Re: Query on invalid objects [message #589690 is a reply to message #589688] Wed, 10 July 2013 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no such job.
You can:
1/ just create one using utl_recomp package.
2/ forget about it, why do you want to compile invalid objects?

Regards
Michel
Re: Query on invalid objects [message #589699 is a reply to message #589690] Wed, 10 July 2013 02:30 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
To expand on Michels point 2 - objects marked as invalid are auto compiled when accessed anyway. When you apply changes to a DB it's worth compiling everything to make sure everything does compile, but other than that I really don't see a need.
Re: Query on invalid objects [message #589757 is a reply to message #589688] Wed, 10 July 2013 08:48 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1869
Registered: May 2013
Location: World Wide on the Web
Senior Member
suhasdba wrote on Wed, 10 July 2013 11:48
or we need to compile it manually ?

Following would compile all of the invalid objects owned by your schema:-
exec dbms_utility.compile_schema( '<YOUR SCHEMA NAME IN UPPER>')

If you still find objects as invalid, then you have errors in those objects.

Invalid objects will get valid as and when they are executed or accessed. However, if you still want to do it manually(don't know why someone would do that, seems pointless to me) you need to do as stated above.

[Updated on: Wed, 10 July 2013 08:49]

Report message to a moderator

Re: Query on invalid objects [message #590006 is a reply to message #589757] Sat, 13 July 2013 08:33 Go to previous messageGo to next message
suhasdba
Messages: 141
Registered: April 2013
Location: INDIA
Senior Member
Hi Michel and all,

Quote:
forget about it, why do you want to compile invalid objects?


To make objects valid.

Thanks everyone for your explanations.
Re: Query on invalid objects [message #590037 is a reply to message #590006] Sat, 13 July 2013 13:04 Go to previous message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
To make objects valid.


As it has been explained, it is useless, the invalid objects are recompiled at first access and so make valid... if this is possible.
More, it is better to NOT revalidate objects as their invalidation means something has been modified in their environment, something which should NOT happen out of application upgrade. So invalid objects are a sign that something wrong has happen in the database and needs investigation.

Regards
Michel
Previous Topic: Undo a dropped Column from a table
Next Topic: decode with like operator
Goto Forum:
  


Current Time: Wed Jul 30 19:47:38 CDT 2014

Total time taken to generate the page: 0.22986 seconds