Home » SQL & PL/SQL » SQL & PL/SQL » Compile Invalid Objects
Compile Invalid Objects [message #560682] Mon, 16 July 2012 07:10 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hi All,

I am Modifying a table structure, so dependent objects(triggers,packages etc) are getting invalid.
So i thought of compiling those invalid objects which are related with the modified table.
I used below query to get the invalid objects,
select obj.object_name,obj.object_type  from user_objects obj,all_dependencies dep 
where referenced_name='DEPT' and obj.object_name=dep.name and dep.owner='SCOTT' and obj.status='INVALID'


Q1)What is wrong with this query, sometimes it works sometime it doesn't.?
Q2)All_dependencies : does this view show all dependent object on a table even the Invalid one's?
Q3) Is there any alternative to find the dependent invalid objects or even dependent object on a table ?

Thanks in advance


[Updated on: Mon, 16 July 2012 07:13]

Report message to a moderator

Re: Compile Invalid Objects [message #560684 is a reply to message #560682] Mon, 16 July 2012 07:21 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
The obvious problem is that the query just looks for objects that directly reference the table.
If you have a procedure A that calls procedure B and procedure B references the table, any change to the table will invalidate A and B.
Your query though will only list B.
You need to either use a connect by query, or just look for everything that's invalid.
Re: Compile Invalid Objects [message #560685 is a reply to message #560684] Mon, 16 July 2012 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@rishwinger

You can have a look at the following script:
http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=1236
http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=1237

Also you don't need to recompile dependent objects, Oracle will automatically do it when needed.

Regards
Michel

[Edit: clickable url]

[Updated on: Tue, 17 July 2012 01:08]

Report message to a moderator

Re: Compile Invalid Objects [message #560754 is a reply to message #560685] Mon, 16 July 2012 13:03 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks both of you....


Well i got something .....

http://www.24x7code.com/main/depandencies.aspx
Re: Compile Invalid Objects [message #560772 is a reply to message #560754] Tue, 17 July 2012 01:07 Go to previous message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My scripts only use SQL, does not need to create a procedure and a table.
Check them.

Regards
Michel
Previous Topic: Table
Next Topic: drop user cascade issue
Goto Forum:
  


Current Time: Wed Sep 17 23:22:42 CDT 2014

Total time taken to generate the page: 0.18893 seconds