Home > DBA, Development > How to recompile a PL/SQL package if it is used by another session?

How to recompile a PL/SQL package if it is used by another session?

 

Imagine you have to recompile a PL/SQL package urgently. In case it is being used and other sessions are executing it , you will not be able to compile this package successfully.

 

What you get is :

ORA-04021: timeout occurred while waiting to lock object error

 

The solution is to find out the locking session and to kill it. Let me say you need to change PCK_TEST package, then:

select * from dba_lock_internal
where lock_id1 like ‘%PCK_TEST%’;

 

Get the session_id and if possible for the system kill that session in order to compile the package.

 

Regards,

Maria

 

Advertisements
Categories: DBA, Development Tags:
  1. AR
    July 5, 2011 at 6:56 pm

    It doesn’t work:

    SQL> select * from dba_lock_internal;
    select * from dba_lock_internal
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

  2. July 5, 2011 at 10:46 pm

    Thanks for reading.
    Are you executing this as a sysdba or regular user? You need to do that as sysdba.

  3. AR
    July 8, 2011 at 7:17 pm

    As sysdba:

    oracle@lissce066/1 – (CLIENT_10.2.0)/SERVER_10.2.0/(CRS_10.2.0)
    >sqlplus ‘/ as sysdba’

    SQL*Plus: Release 10.2.0.3.0 – Production on Fri Jul 8 16:55:04 2011

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
    With the Partitioning, Real Application Clusters and Data Mining options

    SQL> select * from dba_lock_internal;
    select * from dba_lock_internal
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    SQL>

    ————— ————— ————— ————— —————

    root@cv2 # su – oracle
    Sun Microsystems Inc. SunOS 5.9 Generic May 2002
    You have new mail.
    /dev/pts/1
    [oracle@cv2][/home/oracle] export ORACLE_SID=cv
    [oracle@cv2][/home/oracle] sqlplus ‘/ as sysdba’

    SQL*Plus: Release 9.2.0.8.0 – Production on Fri Jul 8 21:15:05 2011

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.8.0 – Production

    SQL> select * from dba_lock_internal;
    select * from dba_lock_internal
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    SQL>

    • July 11, 2011 at 11:19 am

      Strange, which version is your database 9.2 or 10.2.0.3?
      According to the Oracle documentation dba_lock_internal exists in 9.2

      I’ve used in Oracle 10.2, 11.2. In 10.2.0.3 dba_lock_internal view is as follows:

      CREATE OR REPLACE FORCE VIEW SYS.DBA_LOCK_INTERNAL
      (
      SESSION_ID,
      LOCK_TYPE,
      MODE_HELD,
      MODE_REQUESTED,
      LOCK_ID1,
      LOCK_ID2
      )
      AS
      SELECT sid session_id,
      DECODE (TYPE,
      ‘MR’, ‘Media Recovery’,
      ‘RT’, ‘Redo Thread’,
      ‘UN’, ‘User Name’,
      ‘TX’, ‘Transaction’,
      ‘TM’, ‘DML’,
      ‘UL’, ‘PL/SQL User Lock’,
      ‘DX’, ‘Distributed Xaction’,
      ‘CF’, ‘Control File’,
      ‘IS’, ‘Instance State’,
      ‘FS’, ‘File Set’,
      ‘IR’, ‘Instance Recovery’,
      ‘ST’, ‘Disk Space Transaction’,
      ‘TS’, ‘Temp Segment’,
      ‘IV’, ‘Library Cache Invalidation’,
      ‘LS’, ‘Log Start or Switch’,
      ‘RW’, ‘Row Wait’,
      ‘SQ’, ‘Sequence Number’,
      ‘TE’, ‘Extend Table’,
      ‘TT’, ‘Temp Table’,
      TYPE)
      lock_type,
      DECODE (lmode,
      0, ‘None’, /* Mon Lock equivalent */
      1, ‘Null’, /* N */
      2, ‘Row-S (SS)’, /* L */
      3, ‘Row-X (SX)’, /* R */
      4, ‘Share’, /* S */
      5, ‘S/Row-X (SSX)’, /* C */
      6, ‘Exclusive’, /* X */
      TO_CHAR (lmode))
      mode_held,
      DECODE (request,
      0, ‘None’, /* Mon Lock equivalent */
      1, ‘Null’, /* N */
      2, ‘Row-S (SS)’, /* L */
      3, ‘Row-X (SX)’, /* R */
      4, ‘Share’, /* S */
      5, ‘S/Row-X (SSX)’, /* C */
      6, ‘Exclusive’, /* X */
      TO_CHAR (request))
      mode_requested,
      TO_CHAR (id1) lock_id1,
      TO_CHAR (id2) lock_id2
      FROM v$lock /* processes waiting on or holding enqueues */
      UNION ALL /* procs holding latches */
      SELECT s.sid,
      ‘LATCH’,
      ‘Exclusive’,
      ‘None’,
      RAWTOHEX (laddr),
      ‘ ‘
      FROM v$process p, v$session s, v$latchholder h
      WHERE h.pid = p.pid /* 6 = exclusive, 0 = not held */
      AND p.addr = s.paddr
      UNION ALL /* procs waiting on latch */
      SELECT sid,
      ‘LATCH’,
      ‘None’,
      ‘Exclusive’,
      RAWTOHEX (latchwait),
      ‘ ‘
      FROM v$session s, v$process p
      WHERE latchwait IS NOT NULL AND p.addr = s.paddr
      UNION ALL /* library cache locks */
      SELECT s.sid,
      DECODE (ob.kglhdnsp,
      0, ‘Cursor’,
      1, ‘Table/Procedure/Type’,
      2, ‘Body’,
      3, ‘trigger’,
      4, ‘Index’,
      5, ‘Cluster’,
      13, ‘Java Source’,
      14, ‘Java Resource’,
      32, ‘Java Data’,
      TO_CHAR (ob.kglhdnsp))
      || ‘ Definition ‘
      || lk.kgllktype,
      DECODE (lk.kgllkmod,
      0, ‘None’,
      1, ‘Null’,
      2, ‘Share’,
      3, ‘Exclusive’,
      TO_CHAR (lk.kgllkmod)),
      DECODE (lk.kgllkreq,
      0, ‘None’,
      1, ‘Null’,
      2, ‘Share’,
      3, ‘Exclusive’,
      TO_CHAR (lk.kgllkreq)),
      DECODE (ob.kglnaown, NULL, ”, ob.kglnaown || ‘.’)
      || ob.kglnaobj
      || DECODE (ob.kglnadlk, NULL, ”, ‘@’ || ob.kglnadlk),
      RAWTOHEX (lk.kgllkhdl)
      FROM v$session s, x$kglob ob, dba_kgllock lk
      WHERE lk.kgllkhdl = ob.kglhdadr AND lk.kgllkuse = s.saddr;

      CREATE PUBLIC SYNONYM DBA_LOCK_INTERNAL FOR SYS.DBA_LOCK_INTERNAL;
      GRANT SELECT ON SYS.DBA_LOCK_INTERNAL TO SELECT_CATALOG_ROLE;

      You can ensure this object exists:
      select * from dba_objects
      where object_name = ‘DBA_LOCK_INTERNAL’;

      Try also to execute the select statement for the view.

      Regards,
      Maria

  4. AR
    July 11, 2011 at 8:27 pm

    I have a few 8, 9 and 10 DBs and I was testing on 9.2 and 10.2.x. The problem was that I thought this view existed by default on the DB. Now after creating it with your query above the output is different:

    SQL> r
    1* select * from dba_objects where object_name = ‘DBA_LOCK_INTERNAL’

    OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S
    ——————————————————————————————————————— ———————— ——- ——- – – –
    PUBLIC DBA_LOCK_INTERNAL 38608 SYNONYM 2011-07-11:17:50:00 2011-07-11:17:50:00 2011-07-11:17:50:00 VALID N N N

    Thanks

    • July 11, 2011 at 10:49 pm

      That’s good. Happy to hear that 🙂

      Regards,
      Maria

  5. Gayatri
    July 18, 2013 at 3:37 pm

    Hi Maria,

    since in my SYS schema, i dont have the view dba_kgllock, which is used inside DBA_LOCK_INTERNAL. I am unable to create view. Kindly suggest.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: