显示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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/207/viewspace-912207/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/207/viewspace-912207/