This discussion is archived
4 Replies Latest reply: Jul 2, 2012 5:35 AM by Yann39 RSS

library cache pin when accessing apex_application_page_regions

Yann39 Journeyer
Currently Being Moderated
Hi,

Oracle 11.2.0.3.0, APEX 4.1.1.00.23.

I am trying to compile a procedure that access apex_application_page_regions to get the region source of a report :
BEGIN
     -- get report region source
     SELECT region_source
          INTO l_source
          FROM apex_application_page_regions
      WHERE application_id = p_app_id
           AND page_id = p_app_page_id
           AND static_id = p_report; 
     [...]
END;
I can't compile the procedure. After some time I get ORA-04021: timeout occured while waiting to lock object.

From v$session :
STATUS     SERVER          PROGRAM          SQL_EXEC_START          BLOCKING_SESSION_STATUS     BLOCKING_INSTANCE     BLOCKING_SESSION     EVENT               P1TEXT          WAIT_CLASS     STATE
ACTIVE     DEDICATED     plsqldev.exe     07/02/2012 09:12:52     VALID               1               971               library cache pin     handle address     Concurrency     WAITING
I am forced to ask developers to close all APEX instances to kill sessions, to be able to compile the procedure...

Is there any way to bypass this ?

Thank you.

Yann.
  • 1. Re: library cache pin when accessing apex_application_page_regions
    Joel_C Pro
    Currently Being Moderated
    Yann39 wrote:
    Hi,

    Oracle 11.2.0.3.0, APEX 4.1.1.00.23.

    I am trying to compile a procedure that access apex_application_page_regions to get the region source of a report :
    BEGIN
         -- get report region source
         SELECT region_source
              INTO l_source
              FROM apex_application_page_regions
          WHERE application_id = p_app_id
               AND page_id = p_app_page_id
               AND static_id = p_report; 
         [...]
    END;
    I can't compile the procedure. After some time I get ORA-04021: timeout occured while waiting to lock object.

    From v$session :
    STATUS     SERVER          PROGRAM          SQL_EXEC_START          BLOCKING_SESSION_STATUS     BLOCKING_INSTANCE     BLOCKING_SESSION     EVENT               P1TEXT          WAIT_CLASS     STATE
    ACTIVE     DEDICATED     plsqldev.exe     07/02/2012 09:12:52     VALID               1               971               library cache pin     handle address     Concurrency     WAITING
    I am forced to ask developers to close all APEX instances to kill sessions, to be able to compile the procedure...

    Is there any way to bypass this ?

    Thank you.

    Yann.
    I previously had a problem similar to this. (Un)fortunately, the issue appeared to solve itself...

    Library Cache Pin Wait Event (within the context of APEX)
  • 2. Re: library cache pin when accessing apex_application_page_regions
    Yann39 Journeyer
    Currently Being Moderated
    Hi Joel,

    I saw your post, I think the issue does not really "solve itself", the problem disappear as soon as the blocking session ends or get killed.

    I think we can passby it, for example by runing the blocking code inside an "execute immediate" statement, so it will not try any lock at compile time, but it's a bit a makeshift job...

    I don't really understand why Oracle is trying to lock the object, as I only try to compile a procedure with a SELECT statement.
  • 3. Re: library cache pin when accessing apex_application_page_regions
    Joel_C Pro
    Currently Being Moderated
    Have you tried dropping the object altogether?

    Do other DB objects have a dependency on the object you are trying to compile?

    What is the object incidentally - is it the body of a package, a standalone function/procedure or even a trigger?

    edit: I see you've actually said its a procedure. Why not trying packaging it up to see if that makes a difference.

    Edited by: Joel_C on 02-Jul-2012 11:58
  • 4. Re: library cache pin when accessing apex_application_page_regions
    Yann39 Journeyer
    Currently Being Moderated
    It is just a simple procedure, out of a package because it is called from an APEX URL, and need to be public.
    It gets the region source of a report, parse and execute it using DBMS_SQL, and write content to a CSV file.

    However, I was wrong, the problem occured because I use some APEX_COLLECTIONS in the query, and it seems they are not considered when the procedure is called from the URL (or maybe when executed via DBMS_SQL). So the query returned all records (arround 1 million) instead of some undreds, it is why the first time I run it (this morning), it took more than 3 hours to complete ,and it was locked.
    I don't saw it because I forgot to look at APEX_PUBLIC_USER sessions...

    I can compile without any problem now, I simply have to be careful because I don't have privileges for killing sessions.

    Thank you for trying to help Joel.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points