Search This Blog

Monday, February 18, 2013

Find Locks on Packages/Tables and Release them:


Find Locks on Packages/Tables and Release them:

SELECT l.session_id||','||v.serial# sid_serial,
       l.ORACLE_USERNAME ora_user,
       o.object_name,
       o.object_type,
       DECODE(l.locked_mode,
          0, 'None',
          1, 'Null',
          2, 'Row-S (SS)',
          3, 'Row-X (SX)',
          4, 'Share',
          5, 'S/Row-X (SSX)',
          6, 'Exclusive',
          TO_CHAR(l.locked_mode)
       ) lock_mode,
       o.status,
       to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
FROM dba_objects o, gv$locked_object l, v$session v
WHERE o.object_id = l.object_id
      and l.SESSION_ID=v.sid
order by 2,3;

SELECT * FROM DBA_DDL_LOCKS
WHERE NAME IN ('XXX_OBJECT_NAME' ,'XXX_OBJECT_NAME')

SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME IN ('XXX_OBJECT_NAME' ,'XXX_OBJECT_NAME')

SELECT * FROM SYSTEM.V$SESSION
WHERE SID = 2883 OR ROW_WAIT_OBJ# IN (374295,373803,382481,382490)

ALTER SESSION SET CURRENT_SCHEMA=SYS

ALTER SYSTEM KILL SESSION '1586,395'

ALTER SYSTEM FLUSH BUFFER_CACHE

ALTER SYSTEM FLUSH SHARED_POOL

ALTER SESSION SET CURRENT_SCHEMA=APPS

No comments:

Post a Comment