顯示v$sqltext中完整的sql資訊

下面的sql，利用v$session檢視裡的 PREV_SQL_ADDR， PREV_HASH_VALUE列，確定某個SID或者spid對應的完整SQL[@more@]

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

或者以作業系統的process ID為引數。

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.paddr = (select addr
from v$process c
where c.spid = '&spid'
)
)
order by piece asc