oracle 清除v$sql,Oracle 跟踪SQL 查询 及清除 v$SqlArea

这是亲测最新版本, 增加 只跟踪  新增,修改,删除等脚本

--清除 v$SQLArea

altersystem flush shared_pool------------------------------完整跟踪

selecta.FIRST_LOAD_TIME

, a.LAST_ACTIVE_TIME

,a.*

fromv$sqlarea awhere 1=1

and a.LAST_ACTIVE_TIME >= to_date('2014-12-24 14:12:00','yyyy-MM-dd HH24:mi:ss')--and a.LAST_ACTIVE_TIME < to_date('2014-11-25 09:41:59','yyyy-MM-dd HH24:mi:ss')

AND (a.MODULE is null or lower(nvl(a.MODULE,'')) <> 'pl/sql developer')--and sql_text like '%insert%'

and not (lower(sql_fulltext) like '%qdi_modules%'

or lower(sql_fulltext) like '%qdi_host %'

or lower(sql_fulltext) like '%sm_operatelog%'

or lower(sql_fulltext) like '%sys.scheduler$_job%'

or lower(sql_fulltext) like '%sys.scheduler$_job_run_details%'

or lower(sql_fulltext) like '%sys.scheduler$_event_log%')order by a.last_active_time asc

-------------------------------------仅对增删改跟踪

selecta.FIRST_LOAD_TIME

, a.LAST_ACTIVE_TIME

,a.*

fromv$sqlarea awhere 1=1

and a.LAST_ACTIVE_TIME >= to_date('2014-12-24 14:12:00','yyyy-MM-dd HH24:mi:ss')--and a.LAST_ACTIVE_TIME < to_date('2014-11-25 09:41:59','yyyy-MM-dd HH24:mi:ss')

AND (a.MODULE is null or lower(nvl(a.MODULE,'')) <> 'pl/sql developer')and ( lower(sql_fulltext) like '%insert%' or lower(sql_fulltext) like '%update%' or lower(sql_fulltext) like '%delete %')and not (lower(sql_fulltext) like '%qdi_modules%'

or lower(sql_fulltext) like '%qdi_host %'

or lower(sql_fulltext) like '%sm_operatelog%'

or lower(sql_fulltext) like '%sys.scheduler$_job%'

or lower(sql_fulltext) like '%sys.scheduler$_job_run_details%'

or lower(sql_fulltext) like '%sys.scheduler$_event_log%')order by a.last_active_time asc

--------------------------------------------------------------------------

以下为以前使用的.

清除 v$SQLArea

alter system flush shared_pool

select

a.FIRST_LOAD_TIME

, a.LAST_ACTIVE_TIME

,a.*

from v$sqlarea a

where 1=1

and a.LAST_ACTIVE_TIME >= to_date('2014-08-26 16:40:00','yyyy-MM-dd HH24:mi:ss')

and a.LAST_ACTIVE_TIME < to_date('2014-08-26 16:41:59','yyyy-MM-dd HH24:mi:ss')

AND MODULE <> 'PL/SQL Developer'

--and sql_text like '%insert%'

order by a.last_active_time asc