【Oracle】Oracle查看、删除、执行计划

---

一、查询等待事件

SELECT sid
      ,serial#
      ,sql_id
      ,username
      ,a.sql_child_number
      ,a.event
      ,a.osuser
  FROM v$session a
 WHERE a.status = 'ACTIVE'
   AND a.osuser = 'tWX1014920';

或者：

select * from SYS.V_$SQL t
WHERE t.SQL_ID = 'bvjq9rfnrqr7g'

二、查询执行计划

SELECT * FROM TABLE(dbms_xplan.display_cursor());

三、在缓存池中把该执行计划删掉

exec sys.dbms_shared_pool.purge('0000001A83897589,3781295557','c');

四、查锁表

------20210428-1014，赵博
SELECT '''' || s.sid || ',' || s.serial# || ',@' || s.inst_id || '''' AS si_id
      ,o.object_id
      ,o.object_name
      ,o.owner
      ,s.sql_id
       --   ,sq.SQL_FULLTEXT
      ,s.sql_exec_start
      ,s.*
  FROM gv$locked_object l
      ,dba_objects      o
      ,gv$session       s --, gv$sql sq
 WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
   AND l.inst_id = s.inst_id
      -- and s.SQL_ID = sq.SQL_ID
   AND o.owner LIKE ('ETS%')
-- and o.owner = upper(table_owner)
-- and o.object_name = upper('ets_ece_reconc_ref_t')
;