select b.instance_number,
       b.snap_id,
       a.begin_interval_time,
       a.end_interval_time,
       b.sql_id,
       b.plan_hash_value,
       b.parsing_schema_name,
       b.executions_delta,
       b.rows_processed_delta,
       round(b.elapsed_time_delta / decode(b.executions_delta,0,1,b.executions_delta),0) as avg_elapsed_time,
       round(b.cpu_time_delta / decode(b.executions_delta,0,1,b.executions_delta),0) as avg_cpu_time,
       round(b.buffer_gets_delta / decode(b.executions_delta,0,1,b.executions_delta),0) as avg_buffer_gets,
       round(b.iowait_delta/decode(b.executions_delta,0,1,b.executions_delta),0) as avg_iowait,
       round(b.disk_reads_delta/decode(b.executions_delta,0,1,b.executions_delta),0) as avg_disk_reads,
       round(b.ccwait_delta/decod



select dt.owner,

       dt.table_name tablename,

       dc.comments,

       dt.num_rows,

       dt.last_analyzed,

       bb.tab_size_GB table_size_GB,

       bb.index_size_GB index_size_GB,

       decode(bb.islob, 0, null, 'LOB') islob,

       dt.temporary

  from dba_tables dt,

       dba_tab_comments dc,

       (select table_owner,

               table_name,

               round(sum(tab_size_GB) / power(1024, 3), 2) as tab_size_GB,

               round(sum(index_size_GB) / power(1024, 3), 2) as index_size_GB,

               sum(IsLob) as IsLob

          from (select ss.owner table_owner,

                       NVL(dl.table_name, ss.segment_name) table_name,

                       ss.bytes tab_size_GB,

                       0 index_size_GB,

                       decode(dl.table_name, null, 0, 1) IsLob

                  from dba_segments ss, dba_lobs dl

                 where ss.owner = dl.owner(+)

                   and ss.segment_name = dl.segment_name(+)

                   and (ss.segment_type like 'TABLE%' or

                       ss.segment_type LIKE 'LOB%')

                union all

                select ind.table_owner,

                       ind.table_name,

                       0,

                       ss.bytes        index_size_GB,

                       0               IsLob

                  from dba_segments ss, dba_indexes ind

                 where ind.owner = ss.owner

                   and ind.index_name = ss.segment_name)

         group by table_owner, table_name) bb

 where dt.owner not in (select username from dbmgr.sys_users)

   and dt.owner not in ('GGMGR', 'APPMGR')

   and dt.owner = bb.table_owner

   and dt.table_name = bb.table_name

   and dt.owner = dc.owner(+)

   and dt.table_name = dc.table_name(+)



http://www.dataguru.cn/article-10217-1.html