This content has been marked as final. Show 7 replies
This might help you *How to analyze ORA-04021 or ORA-4020 errors? [ID 169139.1]*
hi try to google about
Libary Cache Pin
select 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 from x$kglpn kpin, v$session ss, x$kglob kl, v$session_wait seswait where kpin.kglpnuse = ss.saddr and kpin.kglpnhdl = kl.kglhdadr and kl.kglhdadr = seswait.p1raw and seswait.event = ‘library cache pin’
maybe it's used by other session
You can identify the session id by using the query above.
SELECT S.SID SID, S.USERNAME USERNAME, S.MACHINE MACHINE, L.TYPE TYPE FROM V$LOCK L, V$SESSION S, DBA_OBJECTS O WHERE L.SID = S.SID AND O.OBJECT_ID(+) = L.ID1 AND O.OBJECT_NAME = 'MY_PACKAGE_NAME' ORDER BY S.SID;
Edited by: orawiss on Jul 29, 2010 5:45 PM
Is there any process or job that is accessing this package while you are trying to compile this object and hence causing a lock.
Earlier, when you were trying to compile the package, it was in use by some other session.1 person found this helpful
As you restarted your instance, that session was killed and your package got "free" and you were able to compile it.
Dear user13051169,1 person found this helpful
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.
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;
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.
ORA-04021: timeout occurred while waiting to lock object SCHEMA_NAME.OBJECT_ID
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.