通过os系统进程号如何查出某个update语句的具体内容

来源：互联网发布：罗大佑知乎编辑：程序博客网时间：2024/05/13 17:26

[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

0 0