v$session 会话相关SQL

v$session常用字段如下
SID
SERIAL#
SPID
SQL_ID
STATUS
ROW_WAIT_OBJ#
BLOCKING_SESSION_STATUS

由上图可以看到持锁的sid为80，
Blocking session status：
*VALID 状态valid为正在等待
*NO HOLDER
*GLOBAL
*NOT IN WAIT
*UNKNOWN

查看当前用户的sid和serial#

select sid, serial#, status from v$session where audsid=userenv('sessionid');

查看当前的sid和serial#

select userenv('sid') from dual;

查看当前用户的spid:

select spid from v$process p, v$session s where s.audsid=userenv('sessionid') and s.paddr=p.addr;
select spid from v$process p join v$session s on p.addr=s.paddr and s.audsid=userenv('sessionid');

在v$session中查看被锁的SQL_ID，锁的地址，语句状态，被锁的object_id，datafile_id,被锁的行(第多少行)

SELECT SQL_ID,LOCKWAIT,STATUS,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_ROW# FROM v$session WHERE STATUS = 'ACTIVE' AND sql_id IS NOT NULL;

根据上面的SQL_ID查询被锁的SQL

SELECT * FROM V_$SQL vs WHERE sql_id='10qa7qv1f1tbv';

根据上面的SQL_ID查询被锁object_name

SELECT OWNER,OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID='100866';

在v$session会话中还可以查询sid,serial#，然后根据这两个唯一的标识符，kill掉会话进行解锁

session相关SQL查看
https://www.iteye.com/blog/wujay-1961557