Re: recompile invalid

From: <n.a.ekern_at_usit.uio.no>
Date: 1 Nov 2001 22:39:47 GMT
Message-ID: <9rsivj$9l9$1@readme.uio.no>

Hi!

> But the result of it that there are still some invalid objects. When I
> run it once more sometimes there are more, sometimes there are less
> invalid objects in the database. I don't know how to ensure that all
> objects are OK (teoretically they should be).

I can tell you why sometimes there are more, sometimes there are less invalid objects in the database. It's because this

         ELSIF inv_obj.object_type like 'PACKAGE%' THEN
                 command := 'alter package '||inv_obj.object_name||' 

compile';

will recompile both the package header and the body. And recompilation of package header invalidates all dependent objects. Like in our database, a package that is much used, if I compile the body then no other objects are invalidated, but compiling the hole package invalidates 80 other objects:

SQL> set head on
SQL> select count(*) from user_objects where status='INVALID';   COUNT(*)

---

         0

1 row selected.

SQL> alter package pk_much_used_package compile body;

Package body altered.

SQL> select count(*) from user_objects where status='INVALID';   COUNT(*)

---

         0

1 row selected.

SQL> alter package pk_much_used_package compile;

Package altered.

SQL> select count(*) from user_objects where status='INVALID';   COUNT(*)

---

        80

1 row selected.

SQL> So what you should do is, number indicating priority: 3) use:

         ELSIF inv_obj.object_type = 'PACKAGE' THEN
                 command := 'alter package '||inv_obj.object_name||' 
compile';
         ELSIF inv_obj.object_type = 'PACKAGE BODY' THEN
                 command := 'alter package '||inv_obj.object_name||' 

compile body';

2) in addition to 3), use the decode-function and add some order by in your cursor to order the rows so that 'VIEW' and 'PACKAGE' are handled before the others (recompiling the package will also recompile the body, so you might end up doing some unnecessary recompiling of package bodies because the body maybe already was successfully compiled along with the header)

1. use the dbms_utility or the script already mentioned, or search the web for a good script.

Using 3) or 2) will help so that after running your script you end up with less or the exact same invalid objects.

Njål A. Ekern Received on Thu Nov 01 2001 - 16:39:47 CST