Oracle 根据SQL_ID查询并杀会话，清空执行计划缓冲池

1. 查询最近五分钟内最高频次SQL，查看event
select t.SQL_OPNAME,t.SQL_ID,count(*) from v$active_session_history t where t.SAMPLE_TIME>to_timestamp('20180906-09:55:00','YYYYMMDD-hh24:mi:ss') and t.SESSION_TYPE='FOREGROUND' group by t.SQL_OPNAME,t.SQL_ID order by count(*) desc;

2、 根据SQL 查询到操作用户
select s.username from v$active_session_history t,dba_users s  where t.USER_ID=s.user_id and t.SQL_ID='0nx7fbv1w5xg2';

3、查询并获取当前sql的杀会话语句
select 'alter system kill session '''|| t.SID||','||t.SERIAL#||''';' from v$session t where t.SQL_ID='0nx7fbv1w5xg2';

4、查询并获取当前会话的执行计划清空过程语句
select SQL_TEXT,sql_id, address, hash_value, executions, loads, parse_calls, invalidations from v$sqlarea where sql_id='0nx7fbv1w5xg2';
call sys.dbms_shared_pool.purge('0000000816530A98,3284334050','c');

select SQL_TEXT,sql_id, address, hash_value, executions, loads, parse_calls, invalidations  
from v$sqlarea  where sql_id='1ubrfsjjn7016';

select sql_id, address, hash_value, executions, loads, parse_calls, invalidations  
from v$sqlarea  where sql_id='1ubrfsjjn7016';

call sys.dbms_shared_pool.purge('0700010DF10C9C08,1665368102','c');

select name,created,category,sql_text from dba_sql_profiles where name like '%1ubrfsjjn7016%' order by created desc;

begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_1ubrfsjjn7016_3135409287');
end;
/