Library Cache Pin for over 600 seconds

During my on-call shift today, I recieved the alerts for a database: Library Cache Pin for over 600 seconds

Checked the database, found the following:

===> check session wait: sid=852 wait for library cache pin


SID USERNAME EVENT P1 P2 WAIT_TIME STATE
------ --------------- -------------------- ---------- ---------- ---------- -------------------

852 MTZUSG12CON library cache pin 1.3835E+19 1.3835E+19 0 WAITING


===> current running sql:

SQL_TEXT SID USER_NAME
---------------------------------------- ------ ------------
update AC_PROCESSING_ACCOUNTING set FIL 852 MTZUSG12CON
E_STATUS='CN' where (((FPFC_NXT_PGM_NAME
=:b0 and FILE_STATUS='RD') and FPFC_NXT_
FILE_ALIAS=:b1) and DATA_GROUP like :b2)


From Google, found Burleson has a tip for this topic. click this.

The following script is published by Mark Bobak for locating the source of library cache pin waits.

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, 'MATERIALIZED VIEW',
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE', 59, 'RULE',
                      62, 'EVALUATION CONTEXT',
                     'UNDEFINED') object_type,
       lob.KGLNAOBJ object_name,
       pn.KGLPNMOD lock_mode_held,
       pn.KGLPNREQ lock_mode_requested,
       ses.sid,
       ses.serial#,
       ses.username
  FROM
       x$kglpn pn,
       v$session ses,
       x$kglob lob,
       v$session_wait vsw
  WHERE
   pn.KGLPNUSE = ses.saddr and
   pn.KGLPNHDL = lob.KGLHDADR
   and lob.kglhdadr = vsw.p1raw
   and vsw.event = 'library cache pin'
order by lock_mode_held desc
/

I run it as sysdba and the output is:

OBJECT_TYPE
------------------
OBJECT_NAME
----------------------------------------------------------------------------------------------------------------------                                       --
LOCK_MODE_HELD LOCK_MODE_REQUESTED    SID    SERIAL# USERNAME
-------------- ------------------- ------ ---------- ---------------
NEXT OBJECT
update AC_PROCESSING_ACCOUNTING  set FILE_STATUS='CN' where (((FPFC_NXT_PGM_NAME=:b0 and FILE_STATUS='RD') and FPFC_NX                                       T_
FILE_ALIAS=:b1) and DATA_GROUP like :b2)
             3                   0    985      28668 MTZUSG08CON

NEXT OBJECT
update AC_PROCESSING_ACCOUNTING  set FILE_STATUS='CN' where (((FPFC_NXT_PGM_NAME=:b0 and FILE_STATUS='RD') and FPFC_NX                                       T_
FILE_ALIAS=:b1) and DATA_GROUP like :b2)
             0                   2    852      14208 MTZUSG12CON

After a while, the wait envet went away by itself.