select
dbms_lob.substr(temp.sq)
from
(
select /*dbms_lob.substr(*/a.SQL_FULLTEXT/*)*/ sq,a.PARSING_SCHEMA_NAME, a.LAST_ACTIVE_TIME, rownum r
from v$sqlarea a
where a.PARSING_SCHEMA_NAME in ('S580A')
and a.LAST_ACTIVE_TIME > to_date('2013/3/8 9:24:25','yyyy/mm/dd HH24:mi:ss')
and a.LAST_ACTIVE_TIME < to_date('2013/3/8 9:24:55','yyyy/mm/dd HH24:mi:ss')
order by a.LAST_ACTIVE_TIME desc
)temp
where temp.r != 16 and temp.r < 55;--对于太大的sql,dbms_lob.substr()函数,会有错误,此处可用二 分法逐步排除 大SQL。下面的sql 选出被 上面sql 排除的大sql(查看时,点击单元格后面的 ... ))。
select
temp.sq
from
(
select /*dbms_lob.substr(*/a.SQL_FULLTEXT/*)*/ sq,a.PARSING_SCHEMA_NAME, a.LAST_ACTIVE_TIME, rownum r
from v$sqlarea a
where a.PARSING_SCHEMA_NAME in ('S580A')
and a.LAST_ACTIVE_TIME > to_date('2013/3/8 9:24:25','yyyy/mm/dd HH24:mi:ss')
and a.LAST_ACTIVE_TIME < to_date('2013/3/8 9:24:55','yyyy/mm/dd HH24:mi:ss')
order by a.LAST_ACTIVE_TIME desc
)temp
where temp.r = 16 ;
工具-会话-定义会话查询(一个扳手一样的图标)-详细资料-sql文本修改为如下内容
select sql_text from v$sqltext_with_newlines
where address = hextoraw(decode(:sql_hash_value,0,:PREV_SQL_ADDR,:sql_address))
and hash_value = decode(:sql_hash_value,0,:prev_hash_value,:sql_hash_value)
order by piece
/* concatenate */