Oracle DB is getting locked up

I am beginning to get this strange behavior in my Oracle DB (UAT environment) recently, your feedback is much appreciated.

Recently, when I try to recompile my package, compilation fails to complete and from then on, the DB goes into a "locked state" - I think any query requiring access to this package functions/procedures don't run to completion.

My DBA is saying, I was trying to compile the package while there was an open session trying to access it. That first deleted the existing package and then couldn't recreate the new one and so the entire package went missing and hence the DB got ruined.

I am not sure I quite understand this. Can an "Open Session" cause an issue like this?

There is access to my DB from these sources:

 a) .NET web application (Read/Write access)
 b) Tableau server (Read Only access)
 c) Developers on SQLDbx and PLSQLDeveloper (some with R/W and some with R/O access)
 d) Tableau Desktop dashboard tool (R/O access)

The only change that happened recently is in c) above, where we added a few developers with R/O access. I am not sure how they or anyone else above could leave an "open session" that could cause this.

1 answer

  • answered 2018-02-13 04:53 Connor McDonald

    "Open session" is perhaps not the correct term here. You most likely encountered a block on what we call a "library cache pin". This occurs when the PL/SQL object you are trying to compile was in an active call. If you have PL/SQL programs that run for minutes (hours? days?) at a time, then obviously you can be at risk here.

    For example, in 1 session I do:

    SQL> create or replace
      2  procedure long_running is
      3  begin
      4    dbms_lock.sleep(20);
      5  end;
      6  /
    
    Procedure created.
    
    SQL>
    SQL> exec long_running
    [takes 20 seconds obviously]
    

    and in another session I try to modify the proc

    SQL> create or replace
      2  procedure long_running is
      3  begin
      4    dbms_output.put_line('blah blah blah');
      5  end;
      6  /
    

    and it blocks....If I look at session details I'll see

    SQL> select event, seconds_in_wait
      2  from v$Session
      3  where status = 'ACTIVE'
      4  and username = 'SCOTT'
      5  /
    
    EVENT                                                            SECONDS_IN_WAIT
    ---------------------------------------------------------------- ---------------
    PL/SQL lock timer                                                             16
    library cache pin                                                             12
    

    I see the running one, and the session stuck on library cache pin.

    But if you just wait...eventually you'll be able to compile once the other execution finishes.

    Obviously if you have routines that run for hours...then you should have a means of being able to stop them gracefully.