CREATE OR REPLACE PROCEDURE solve_lock
As
V_sql VARCHAR2 (3000); --Define the SQL statement that V_sql accepts the fetch lock
Cur_lock Sys_refcursor; --Define a cursor variable, loop the SQL statement that executes the FETCH lock
Type Tp_lock is record (--a variable that defines the record type
V_sid number,
V_type VARCHAR2 (10),
V_ID1 number,
V_id2 number,
V_lmode VARCHAR2 (200),
V_request VARCHAR2 (200),
V_lock_time number,
V_block number);
Records_lock Tp_lock;
BEGIN
V_sql:= ' SELECT Sid,type,id1,id2,
DECODE (lmode,0, ' NONE ', 1, ' NULL ', 2, ' Row SHARE ', 3, ' Row EXCLUSIVE ', 4, ' SHARE ', 5, ' SHARE ROW EXCLUSIVE ', 6, ' EXCLUSIVE ', ' HAHA ') Lock_type,
DECODE (request,0, ' NONE ', 1, ' NULL ', 2, ' Row SHARE ', 3, ' Row EXCLUSIVE ', 4, ' SHARE ', 5, ' SHARE ROW EXCLUSIVE ', 6 , ' EXCLUSIVE ', ' HAHA ') lock_request,
Ctime,block from V$lock WHERE TYPE in (' TM ', ' TX ') ';
OPEN Cur_lock for V_sql;
LOOP
FETCH Cur_lock into Records_lock;
EXIT when Cur_lock%notfound;
IF Records_lock. V_request <> ' NONE ' then-crawl the session that made the request lock
Dbms_output. Put_Line (' SID: ' | | Records_lock. v_sid| | ' is request a lock, Lock_mode ' | | Records_lock. v_request| | ' and being locked ' | | Records_lock. v_lock_time| | ' S ');
END IF;
IF Records_lock. V_block <> 0 Then-crawl session with lock blocking
Dbms_output.put_line (' SID: ' | | Records_lock. v_sid| | ' is make a lock, Lock_mode is ' | | Records_lock. V_lmode);
END IF;
END LOOP;
CLOSE Cur_lock;
END Solve_lock;
/
FETCH LOCK SQL statement-Third modification