FETCH LOCK SQL statement-fourth time modification

--Completion variable V_BLOCKING_SID is used to dynamically crawl the session ID that generated the lock, the output parameter has no problem, but the execution error identifier is invalid!

CREATE OR REPLACE PROCEDURE solve_lock123

As

V_sql VARCHAR2 (3000); --Define the SQL statement that V_sql accepts the fetch lock
V_sql02 VARCHAR2 (3000);
V_SQL03 VARCHAR2 (3000);

KILL_SID number;
Kill_serial number;

Cur_lock Sys_refcursor; --Define a cursor variable, loop the SQL statement that executes the FETCH lock
Cur_lock02 Sys_refcursor;


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;

TYPE tp_lock02 is RECORD (
Waiting_sid number,
Waiting_sql VARCHAR2 (1000),
Blocker_event VARCHAR2 (1000),
Blocking_sid number,
Blocking_sql VARCHAR2 (1000));
Records_lock02 tp_lock02;

V_BLOCKING_SID number;
V_WAITING_SID number;

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 (' Waiting sid: ' | | Records_lock. v_sid| | ' is request a lock, Lock_mode ' | | Records_lock. v_request| | ' and being locked ' | | Records_lock. v_lock_time| | ' S ');
V_waiting_sid:=records_lock. V_sid;
END IF;

IF Records_lock. V_block <> 0 Then-crawl session with lock blocking
Dbms_output.put_line (' Blocking sid: ' | | Records_lock. v_sid| | ' is make a lock, Lock_mode is ' | | Records_lock. V_lmode);
V_blocking_sid:=records_lock. V_sid;
Dbms_output.put_line (V_BLOCKING_SID);
END IF;
END LOOP;
CLOSE Cur_lock;


V_sql02:= ' SELECT DISTINCT
Waiter. SID "Waiting_sid",
W_sql. Sql_text "SQL from Waiting SESSION",

Blocker. Event "Blocker event",
Blocker. Sid "BLOCKING Sid",

B_sql. Sql_text "SQL from BLOCKING SESSION"

From V$session Waiter,
V$session blocker,
V$sql W_sql,
V$sql B_sql
WHERE Waiter. Event= ' Enq:tx-row lock contention ' and waiter. Blocking_session=blocker. Sid
and W_sql. Sql_id=waiter. sql_id
and B_sql. Sql_id =nvl (blocker. Sql_id,blocker. prev_sql_id) ';

OPEN cur_lock02 for V_SQL02;
LOOP
FETCH cur_lock02 into records_lock02;
EXIT when Cur_lock02%notfound;

IF records_lock02. Waiting_sid is isn't NULL then
Dbms_output.put_line (' Waiting sid: ' | | Records_lock02. waiting_sid| | ' Wait_sql is: ' | | Records_lock02. WAITING_SQL);
END IF;

IF records_lock02. Blocking_sid is isn't NULL then
Dbms_output.put_line (' Blocking sid: ' | | Records_lock02. blocking_sid| | ' Block_sql is: ' | | Records_lock02. BLOCKING_SQL);
END IF;



END LOOP;

CLOSE cur_lock02;

Dbms_output.put_line (V_BLOCKING_SID);
--resolve lock blocking, lock wait
V_sql03:= ' SELECT SID, serial# from v$session WHERE sid=v_blocking_sid ';

EXECUTE IMMEDIATE v_sql03 into kill_sid,kill_serial;
--' SELECT SID, serial# from v$session WHERE sid=197 ' into kill_sid,kill_serial;

Dbms_output.put_line (' ALTER SYSTEM KILL SESSION (' | | kill_sid| | ', ' | | kill_serial| | ');



END solve_lock123;

FETCH LOCK SQL statement-fourth time modification