FETCH LOCK SQL statement-sixth time modification

Increase exception handling

CREATE OR REPLACE PROCEDURE solve_lock

As

V_sql VARCHAR2 (3000); --Define the SQL statement that V_sql accepts the fetch lock
V_sql02 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

Dbms_output. Put_Line ('------------------find the database for lock blocking, lock waiting------------------');
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;
END IF;

END LOOP;
CLOSE Cur_lock;

Dbms_output. Put_Line ('------------------find the session that generated the lock, the session------------------------' lock-waiting);
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 ('------------------resolve lock blocking, lock wait------------------');

SELECT sid,serial# into kill_sid,kill_serial from V$session WHERE sid=v_blocking_sid;

Dbms_output.put_line (' Action:alter SYSTEM KILL SESSION (' | | | kill_sid| | ', ' | | kill_serial| | ');

EXCEPTION when No_data_found Then
Dbms_output. Put_Line (sqlcode| | SQLERRM);

END Solve_lock;

FETCH LOCK SQL statement-sixth time modification