用SYS用户登录数据库!
查看锁表进程SQL语句1:
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id =lo.object_id and lo.session_id = sess.sid;
查看锁表进程SQL语句2:
SELECT DISTINCT S.SID , /*会话的唯一标识,通常要对某个会话进行分析前,首先就需要获得该会话的SID。*/
--S.SERIAL# /*会话的序号*/,
S.STATE /*WAIT STATE~*/ ,
S.BLOCKING_SESSION ,
--SESSION IDENTIFIER OF THEBLOCKING SESSION. THIS COLUMN IS VALID ONLY IF BLOCKING_SESSION_STATUS HAS THEVALUE VALID.
S.BLOCKING_SESSION_STATUSSTATUS, /*THIS COLUMN PROVIDES DETAILS ON WHETHER THERE IS A BLOCKING SESSION:*/
( CASE
WHEN SQL_TEXT IS NULL/*LO.REQUEST = 0 */
THEN
'(SID:' ||S.SID || ')会话 SQL已跑完'
ELSE
'(SID:' ||S.SID || ')会话 正执行SQL:' || SQL_.SQL_TEXT
END ) SQL_TEXT/*执行完的SQL'SQL_TEXT标记SQL已跑完,否则标记SQL'*/ ,
--SQL_.SQL_FULLTEXT SQL全文本,
S.USERNAME /*创建该会话的用户名*/ ,
O.OWNER || '.'|| O.OBJECT_NAME 锁的对象, --V$SESSION.ROW_WAIT_OBJ#若操作完的该字段值=-1,所以关联的V$LOCKED_OBJECT取锁表
LO.REQUEST , -- Lock mode in which the process requeststhe lock 会话申请的锁的模式
S.EVENT ,
S.MACHINE /*客户端的机器名。*/ ,
S.LOGON_TIME/*登陆时间*/ ,
'ALTER SYSTEMKILL SESSION ''' || S.SID || ',' || S.SERIAL# || ''';' KILL --若存在锁情况,会用到KILL锁释放~
FROM V$SESSION S
LEFT JOIN V$SQL SQL_
ON SQL_.SQL_ID = S.SQL_ID
JOIN V$LOCKED_OBJECT L
ON L.SESSION_ID = S.SID
JOIN ALL_OBJECTS O
ON L.OBJECT_ID = O.OBJECT_ID
JOIN V$LOCK LO
ON (LO.BLOCK != 0 OR LO.REQUEST != 0 )
--V$LOCK.block => A value of either 0 or1, depending on whether or not the lock in question is the blocker
--V$LOCK.REQUEST =>Lock mode in which the process requests the lock:下文有值的意义~['0 - none']
WHERE LO.SID = L.SESSION_ID
AND LO.SID = S.SID
ORDER BY S.BLOCKING_SESSION DESC ;