实验环境

搭建平台：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;