FETCH LOCK SQL statement-Third modification

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.