【TUNE_ORACLE】查出所有有“select *”語句的SQL參考

Attack_on_Jager發表於2021-08-04

實驗環境

搭建平臺:VMware Workstation

OS:RHEL 6.10

Grid&DB:Oracle 11.2.0.4


SQL參考

--列出有select * 的SQL

select a.sql_id, a.sql_text, c.owner, d.table_name, d.column_cnt, c.size_mb

  from v$sql a,

       v$sql_plan b,

       (select owner, segment_name, sum(bytes / 1024 / 1024) size_mb

          from dba_segments

         group by owner, segment_name) c,

       (select owner, table_name, count(*) column_cnt

          from dba_tab_cols

         group by owner, table_name) d

 where a.sql_id = b.sql_id

   and a.child_number = b.child_number

   and b.object_owner = c.owner

   and b.object_name = c.segment_name

   and b.object_owner = d.owner

   and b.object_name = d.table_name

   and REGEXP_COUNT(b.projection, ']') = d.column_cnt

   and c.owner = 'TEST'  --使用者名稱

 order by 6 desc;


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69992972/viewspace-2785136/,如需轉載,請註明出處,否則將追究法律責任。

相關文章