[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1877
  • Last Modified:

How do you find locks on Packages, Procedures and Functions?

Hi,
How do you find locks on Packages, Procedures and Functions?
How do you release the locks?

Bill
0
BILL Carlisle
Asked:
BILL Carlisle
3 Solutions
 
schwertnerCommented:
you can find locks on sessions:

REM USAGE:"@path/tfslkill"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on V$LOCK, V$SESSION, SYS.USER$, SYS.OBJ$
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    The report generated by this script gives information on sessions
REM    which are holding locks and gives the information needed to kill
REM    using the ALTER SYSTEM KILL SESSION command.
REM ------------------------------------------------------------------------
REM Main text of script follows:
 
set linesize 300 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username  format a10  heading "Username"
column terminal heading Term format a10
column OSuser heading OSuser format a15
column machine heading Machine format a10
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
column sql format a135 heading "S  Q  L"
select      nvl(S.USERNAME,'Internal') username,
      nvl(S.TERMINAL,'None') terminal,
        nvl(S.MACHINE,'None') machine,
        nvl(S.OSUSER,'None') OSuser,    
      L.SID||','||S.SERIAL# Kill,
      U1.NAME||'.'||substr(T1.NAME,1,20) tab,
      decode(L.LMODE,1,'No Lock',
            2,'Row Share',
            3,'Row Exclusive',
            4,'Share',
            5,'Share Row Exclusive',
            6,'Exclusive',null) lmode,
      decode(L.REQUEST,1,'No Lock',
            2,'Row Share',
            3,'Row Exclusive',
            4,'Share',
            5,'Share Row Exclusive',
            6,'Exclusive',null) request,
        sql. sql_text SQL  
from      V$LOCK L,  
      V$SESSION S,
      SYS.USER$ U1,
      SYS.OBJ$ T1,
        v$sqltext sql
where      L.SID = S.SID  
and      T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)  
and      U1.USER# = T1.OWNER#
and      S.TYPE != 'BACKGROUND'
and     sql.hash_value=s.sql_hash_value  
order by   username, terminal, machine
--order by 1,2,6
/

0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
It doesn't give me an error or any results...
It runs but comes back blank.
0
 
Mark GeerlingsDatabase AdministratorCommented:
To release the locks, you would have to either wait for those sessions to exit, or you would have to kill those sessions.  I use this query (from an earlier posting on this site) to find these "dictionary locks" which are not the same as record locks:
-- from http://www.experts-exchange.com/Databases/Oracle/Q_21730651.html#15916572
prompt " "
prompt Remember to use upper case for all non-Java objects
prompt " "
select owner, substr(name,1,30) "Obj name", type, locks, loads, namespace
from v$db_object_cache where locks > 0 and type <> 'CURSOR'
and name like '&obj_name';
0
 
sdstuberCommented:


try this...  It'll will display a hiearchy tree of which sessions are blocking others.

is your system RAC?  If so, you may have locks on another instance blocking you
and you'll need to use the second query.  If single instance, then just use the first one.


WITH lock_holders AS
     (
         SELECT w.session_id w_sess_id, h.session_id h_sess_id,
                w.lock_type w_lock_type, h.mode_held h_mode_held,
                w.mode_requested w_mode_requested, w.lock_id1 w_lock_id1,
                w.lock_id2 w_lock_id2
           FROM (SELECT session_id, lock_type, mode_requested, lock_id1,
                        lock_id2
                   FROM dba_lock
                  WHERE mode_requested != 'None') w,
                (SELECT session_id, mode_held,lock_type,lock_id1,lock_id2
                   FROM dba_lock
                  WHERE blocking_others = 'Blocking'
                    AND mode_held != 'None'
                    AND mode_held != 'Null') h
          WHERE w.lock_type = h.lock_type
            AND w.lock_id1 = h.lock_id1
            AND w.lock_id2 = h.lock_id2)
SELECT     LPAD(' ', 3 *(LEVEL - 1)) || w_sess_id waiting_session,
           w_lock_type, w_mode_requested, h_mode_held, w_lock_id1, w_lock_id2
      FROM (SELECT *
              FROM lock_holders
            UNION ALL
            (SELECT h_sess_id, NULL, 'None', NULL, NULL, NULL, NULL
               FROM lock_holders
             MINUS
             SELECT w_sess_id, NULL, 'None', NULL, NULL, NULL, NULL
               FROM lock_holders))
CONNECT BY PRIOR w_sess_id = h_sess_id
START WITH h_sess_id IS NULL



/* Multi-instance version of above query */
WITH lock_holders AS
     (
         SELECT w.inst_id w_inst_id, w.session_id w_sess_id,
                h.inst_id h_inst_id, h.session_id h_sess_id,
                w.lock_type w_lock_type, h.mode_held h_mode_held,
                w.mode_requested w_mode_requested, w.lock_id1 w_lock_id1,
                w.lock_id2 w_lock_id2
           FROM (SELECT inst_id, 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
                             (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 gv$lock
                  WHERE request != 0) w,
                (SELECT inst_id, SID session_id,
                        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(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,
                        TO_CHAR(id1) lock_id1, TO_CHAR(id2) lock_id2
                   FROM gv$lock
                  WHERE BLOCK = 1 AND lmode != 0 AND lmode != 1) h
          WHERE w.lock_type = h.lock_type
            AND w.lock_id1 = h.lock_id1
            AND w.lock_id2 = h.lock_id2)
SELECT     LPAD(' ', 3 *(LEVEL - 1)) || w_sess_id waiting_session, w_inst_id,
           h_inst_id, w_lock_type, w_mode_requested, h_mode_held, w_lock_id1,
           w_lock_id2
      FROM (SELECT *
              FROM lock_holders
            UNION ALL
            (SELECT h_inst_id, h_sess_id, NULL, NULL, 'None', NULL, NULL,
                    NULL, NULL
               FROM lock_holders
             MINUS
             SELECT w_inst_id, w_sess_id, NULL, NULL, 'None', NULL, NULL,
                    NULL, NULL
               FROM lock_holders))
CONNECT BY PRIOR w_sess_id = h_sess_id
START WITH h_sess_id IS NULL
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
thank you for the help.. they were already released I got no returns.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now