查看当前sql的SID
select spid,pid from v$process whereaddr=(select paddr from v$session where sid=(select sid from v$mystat whererownum=1));
select sql_text from v$sqlarea where (hash_value,address)=(select sql_hash_value,sql_address from v$session where sid=&sid);
注意,上面的语句SQL脚本只能查出当前用户正在执行SQL,也就是说v$session.status='ACTIVE'
如果想找出执行过了的sql,那么可以使用如下sql
select sql_text from v$sqlarea a,v$session b where a.SQL_ID=b.PREV_SQL_ID and b.SID=&sid;
最终的SQL:
SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr, sql_address) FROM v$session b WHERE b.sid = &sid) ORDER BY piece ASC;