7 Replies Latest reply on Jul 30, 2010 10:25 AM by 618702

    I can't able to compile Package


      I can't able to compile package which contains 7241 lines, when tried to compile i got error has mentioned below

      Note:- my server is (shared server)

      ERROR at line 1:
      ORA-04021: timeout occurred while waiting to lock object schemaname.spkg_hash.

      After shutting down db and started i tried to compile it got compiled still i don't understand what happened in background

      can anyone explain why this happening?

      Edited by: user13051169 on Jul 30, 2010 2:52 AM

      Edited by: user13051169 on Jul 30, 2010 2:55 AM
        • 1. Re: I can't able to compile Package

          This might help you *How to analyze ORA-04021 or ORA-4020 errors? [ID 169139.1]*

          • 2. Re: I can't able to compile Package
            hi try to google about
            Libary Cache Pin
            ss.sid session_id,
            ss.serial# serial_#,
            kl.kglnaobj objectname,
            case kl.kglobtyp
            when 1 then ‘index’
            when 2 then ‘table’
            when 3 then ‘cluster’
            when 4 then ‘view’
            when 5 then ‘synonym’
            when 6 then ‘sequence’
            when 7 then ‘procedure’
            when 8 then ‘function’
            when 9 then ‘package’
            when 11 then ‘package body’
            when 12 then ‘trigger’
            else ‘others’
            end objtype,
            ss.username user_login,
            ss.osuser os_userid,
            ss.program user_program
            x$kglpn kpin, v$session ss,
            x$kglob kl, v$session_wait seswait
            kpin.kglpnuse = ss.saddr and  kpin.kglpnhdl = kl.kglhdadr
            and kl.kglhdadr = seswait.p1raw  and seswait.event = ‘library cache pin’
            • 3. Re: I can't able to compile Package
              maybe it's used by other session
              SELECT S.SID SID,
                     S.USERNAME USERNAME,
                     S.MACHINE MACHINE,
                     L.TYPE TYPE
               WHERE L.SID = S.SID
                 AND O.OBJECT_ID(+) = L.ID1
                 AND O.OBJECT_NAME = 'MY_PACKAGE_NAME'
               ORDER BY S.SID;
              You can identify the session id by using the query above.
              Good luck!

              Edited by: orawiss on Jul 29, 2010 5:45 PM
              • 4. Re: I can't able to compile Package
                Is there any process or job that is accessing this package while you are trying to compile this object and hence causing a lock.
                • 5. Re: I can't able to compile Package
                  Earlier, when you were trying to compile the package, it was in use by some other session.

                  As you restarted your instance, that session was killed and your package got "free" and you were able to compile it.

                  1 person found this helpful
                  • 6. Re: I can't able to compile Package
                    Dear user13051169,

                    Some has already taken a lock on that object. Check the v$session view and find out who is currently accessing it.

                    When you shutdown the database the lock has been released by the PMON and that session did not lock it again before you compile it. That is why you did compile the package body after the shutdown command.

                    I hope that helps.

                    1 person found this helpful
                    • 7. Re: I can't able to compile Package
                      Dear user13051169,

                      You can have it tested if you want to. Open up two sessions and execute the relevant procedure or function. Than on the other session, try to compile the package body. You should have below error afterwards;
                      ORA-04021: timeout occurred while waiting to lock object SCHEMA_NAME.OBJECT_ID
                      Next time i suggest you not to shutdown the database because you don't have to do it. You can just simply find out the locking session and if necessary you can kill it.

                      There is another option for you that you may use the utlrp.sql . The following arcticle mentions how to recompile an object;


                      Here is the quote of the utlrp.sql from the article;

                      +utlrp.sql and utlprp.sql+
                      +The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:+

                      +* 0 - The level of parallelism is derived based on the CPU_COUNT parameter.+
                      +* 1 - The recompilation is run serially, one object at a time.+
                      +* N - The recompilation is run in parallel with "N" number of threads.+

                      +Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.+

                      Hope That Helps.