Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Budhi.

Asked: January 24, 2003 - 12:57 pm UTC

Last updated: January 27, 2003 - 7:07 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

quite often when we are doing some release to any production database, we only compile the procedures or packages that needs to be changed. But a problem that appears along with this mode of operation is several other packages or procedures which used to depend on any of these packages or procedures will become invalid. Then we get into this confused whirl of comiling and recompiling the invalidates packages.

Can u suggest a query which will return me all user objects based on the dependency level, that is the object on which the maximum dependency is there must be recompiled first.

Or is there any DBMS package that will do this for me, I knwo DBMS_UTILITY.COMPIL_SCHEMA is not soing this for me.

Thanks and Regards

Budhi

and Tom said...

Just use packages -- never procedures.

Then, just install all of the new specs.

Then install all of the new bodies.

Then, walk away -- don't both recompiling, it'll take care of itself!


Or, if you have my book -- I have a routine in there that shows you a way to do this. Basically, you just take the "first" invalid thing you see -- compile it, eg:

select * from user_objects where status = 'INVALID' and rownum = 1;
compile it.


Just keep doing that over and over and everything will compile. YOu need not do them in ANY order -- just do them one at a time (and if one fails -- bail out -- or remember you tried to do it and skip it next time)

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1418403973266 <code>

is an example

Rating

  (1 rating)

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

Comments

Compiling invalid objects

Logan Palanisamy, January 27, 2003 - 6:27 pm UTC

Tom,

How about runnnig ORACLE_HOME/rdbms/admin/utlrp.sql
as SYS or SYSTEM in SQL*Plus? Won't that compile all the invalid objects?




Tom Kyte
January 27, 2003 - 7:07 pm UTC

you can do lots of things

Me, well, I'm lazy and don't like sitting staring at consoles -- so I would just let it be.

but the code I provided does basically the same thing for a schema.

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 latest video and Chris's latest video 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