[root@hxy ~]# ps -ef|grep oracle
oracle 25895 25631 0 11:06 pts/2 00:00:00 sqlplus as sysdba
session1 会话执行
SQL> update t1 set object_id=30 where object_id=20;
1 row updated.
通过v$locked_object 视图和OS进程号找sid号,
SQL> select * from v$locked_object where process='25895';
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
---------- ---------- - --------- ---------- ---------- ------------------------------ ------------------------ ----------- - ----------
5 8 731 53616 159 SYS oracle 25895 3
SQL> select PREV_HASH_VALUE, PREV_SQL_ID,PREV_SQL_ADDR
from v$session
where sid=(
select SESSION_ID
from v$locked_object
where process='25895');
PREV_HASH_VALUE PREV_SQL_ID PREV_SQL_ADDR
--------------- ------------- ----------------
1427527461 3s55095ajcpt5 0000000065AB7698
通过v$sqltext或者V$sql视图查询SQL语句
SQL>select * from v$sqltext where SQL_ID ='3s55095ajcpt5';
ADDRESS HASH_VALUES SQL_ID COMMAND_TYPE PIECE SQL_TEXT
--------------- ---------------- ------------ --------- ---------- ----------------------------------------------------------------
0000000065AB7698 1427527461 3s55095ajcpt5 6 0 update t1 set object_id=20 where object_id=30
方法2通过v$sqlarea视图
SQL> select SQL_TEXT,SQL_ID,HASH_VALUE, ADDRESS,OLD_HASH_VALUE ,PLAN_HASH_VALUE from v$sqlarea where SQL_ID=‘3s55095ajcpt5’;
SQL_TEXT SQL_ID HASH_VALUE ADDRESS OLD_HASH_VALUE PLAN_HASH_VALUE
-------------------------------------------------- ---------------- ------------------ --------------------- -------------------------------------- --------------------------------
update t1 set object_id=20 where object_id=30 3s55095ajcpt5 1427527461 0000000065AB7698 3099421063 2927627013