Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to view the locked session information script, the editor thinks it is very practical, so share it for you to do a reference, I hope you can get something after reading this article.

View information about currently blocked objects and locks

SELECT DISTINCT

S1.inst_id BlockingInst

S1.sid BlockingSid

S1.serial# BlockingSerial

S2.inst_id BlockedInst

S2.sid BlockedSid

S2.username BlockedUser

S2.seconds_in_wait BlockedWaitTime

FROM gv$session s1

Gv$lock l1

Gv$session s2

Gv$lock l2

WHERE s1.inst_id = l1.inst_id and

L1.block in (1,2) and

L2.request! = 0 and

L1.sid = s1.sid and

L1.id1 = l2.id1 and

L1.id2 = l2.id2 and

S2.sid = l2.sid and

S2.inst_id = l2.inst_id

ORDER BY 1, 2, 3

View locked reply messages:

SELECT

S.blocking_session

S.sid

S.osuser

S.seconds_in_wait

S.event

S.state

T.sql_text

O.object_name

FROM

V$session s, v$sqltext t, v$locked_object lo, dba_objects o

WHERE

Blocking_session IS NOT NULL

And t.hash_value=s.sql_hash_value

And s.sid=lo.session_id

And lo.object_id=o.object_id

ORDER BY t.hash_value,t.PIECE

In the role of DBA, you can use the following SQL statement to view the locks in the current database:

Col owner for a12

Col object_name for a16

Select b.owner,b.object_name,l.session_id,l.locked_mode

From v$locked_object l, dba_objects b

Where b.object_id=l.object_id

/

Select t2.username,t2.sid,t2.serial#,t2.logon_time, t1.object_id,t3.object_name,t1.locked_mode

From v$locked_object T1 dba_objects session T2 dba_objects T3

Where t1.session_id=t2.sid and t1.object_id=t3.object_id order by t2.logon_time

/

If there is a long-standing column, it may be a lock that is not released. We can use the following SQL statement to kill abnormal locks that have not been released for a long time:

Alter system kill session 'sid,serial#'

There are several other views that can query blocking callbacks:

DBA_WAITERS

DBA_BLOCKERS

V$WAIT_CHAINS

This is the end of this article on "how to view locked session information scripts". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.