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

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

 

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

-- 清除 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-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



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


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

 

      

posted on 2013-05-31 11:41  Alfa 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/wuyifu/archive/2013/05/31/3110074.html