delete

Show blocking sessions

Simple script for SQL*Plus to show blocking and waiting cases: rem ------------------------------------------------------------------------- rem   Show users waiting for a lock, the locker and the rem   SQL-Command they are waiting for a lock rem   osuser, schema and PIDs are shown rem ----------------------------------------------------------------------- rem -- SET PAGES 24 LINES 100 FEED ON ECHO OFF TERMOUT ON HEAD ON COLUMN OS_LOCKER FORMAT A15 TRUNC COLUMN OS_WAITER FORMAT A15 TRUNC COLUMN LOCKER_SCHEMA FORMAT A15 TRUNC COLUMN WAITER_SCHEMA FORMAT A15 TRUNC COLUMN...
delete

Where to find info about locks

DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on DBA_DDL_LOCKS – Shows all DDL locks held or being requested DBA_DML_LOCKS  – Shows all DML locks held or being requested DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock DBA_LOCKS  – Shows all locks or latches held or being requested DBA_WAITERS  – Shows all sessions waiting on, but not holding waited for locks
delete

Show locked objects with user who caused...

Shows all objects even source code. select  OS_USER_NAME os_user, lo.PROCESS os_pid, ORACLE_USERNAME oracle_user, l.SID oracle_id, s.MACHINE, s.TERMINAL, decode(l.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',...
delete

Show exclusive locks on package / functi...

If you cannot compile and save updated source code of package / function etc. you need to find who is blocking it: select distinct s.sid, s.username, l.name object_name, l.mode_held from dba_ddl_locks l, v$session s where l.session_id = s.sid and l.mode_held != 'Null' and l.name = '...your_package_name....'  
delete

Show locked objects

SELECT  l.inst_id, SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID, S.serial#, SUBSTR(O.OWNER||'.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID, DECODE(L.LOCKED_MODE,   0,'NONE', 1,'NULL', 2,'ROW SHARE', 3,'ROW EXCLUSIVE', 4,'SHARE', 5,'SHARE ROW EXCLUSIVE', 6,'EXCLUSIVE', NULL) LOCK_MODE FROM    sys.GV_$LOCKED_OBJECT L , DBA_OBJECTS O , sys.GV_$SESSION S , sys.GV_$PROCESS P WHERE     L.OBJECT_ID = O.OBJECT_ID and     l.inst_id = s.inst_id AND     L.SESSION_ID = S.SID and     s.inst_id = p.inst_id AND     S.PADDR = P.ADDR(+) order by...