getting the sql text of particular process

getting the sql text of particular process

1)pid obtained from OS commands
ps auxgw|grep oracle|head -5
1667200

2)v$session SQL_ADDRESS

use spid from v$process to join with paddr of v$session and addr in v$process to get the sql_address from v$session

select a.serial#,a.spid,b.sid,b.sql_address
from v$process a,v$session b
where a.addr=b.paddr and a.spid='1667200';

SERIAL# SPID SID SQL_ADDRESS
---------- ------------ ---------- ----------------
7 1667200 40 0700000020F9C268

3)Use the SQL_ADDRESS from previous entry to get the sqltext
select * from v$sqltext where address='0700000020F9C268';
ADDRESS HASH_VALUE COMMAND_TYPE PIECE SQL_TEXT
---------------- ---------- ------------ ---------- ----------------------------------------------------------------
0700000020F9C268 1739069764 47 0 begin DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); end;