oracle table ,database growth report script「建议收藏」

@seg_top_logreads_10g.sql
select
    object_name "Object Name"
  , tablespace_name "Tablespace Name"
  , object_type "Object Type"
  , logical_reads_total "Logical Reads"
  , ratio "%Total"
from(
select n.owner||"."||n.object_name||decode(n.subobject_name,null,null,"."||n.subobject_name) object_name
     , n.tablespace_name
     , case when length(n.subobject_name) < 11 then
              n.subobject_name
            else
              substr(n.subobject_name,length(n.subobject_name)-9)
       end subobject_name
     , n.object_type
     , r.logical_reads_total
     , round(r.ratio * 100, 2) ratio
  from dba_hist_seg_stat_obj  n
     , (select *
          from (select e.dataobj#
                     , e.obj#
                     , e.dbid
                     , e.logical_reads_total - nvl(b.logical_reads_total, 0) logical_reads_total
                     , ratio_to_report(e.logical_reads_total - nvl(b.logical_reads_total, 0)) over () ratio
                 from dba_hist_seg_stat  e
                     , dba_hist_seg_stat  b
                 where b.snap_id  = 2694
                   and e.snap_id  = 2707
                   and b.dbid     = 37933856   
                   and e.dbid     = 37933856
                   and b.instance_number  = 1
                   and e.instance_number  = 1
                   and e.obj#             = b.obj#
                   and e.dataobj#         = b.dataobj#
               and e.logical_reads_total - nvl(b.logical_reads_total, 0)  > 0
                 order by logical_reads_total desc) d
          where rownum <= 100) r
 where n.dataobj# = r.dataobj#
SEE CODE DEPOT FOR FULL SCRIPTS

)

order by logical_reads_total desc;

代码100分

代码100分
@table_growth_status 
set serveroutput on     
    execute dbms_output.enable(buffer_size => NULL);**


    Declare
          v_BaselineSize  number(20);
          v_CurrentSize   number(20);
          v_TotalGrowth   number(20);
          v_Space     number(20);
          cursor usageHist is
                  select a.snap_id,
                  SNAP_TIME,
                  sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum
              from
 (select SNAP_ID,
                      sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA
                  from DBA_HIST_SEG_STAT
                  group by SNAP_ID
                  having sum(SPACE_ALLOCATED_TOTAL) <> 0
                  order by 1 ) a,
                  (select distinct SNAP_ID,
                      to_char(END_INTERVAL_TIME,"DD-Mon-YYYY HH24:Mi") SNAP_TIME
                  from DBA_HIST_SNAPSHOT) b
              where a.snap_id=b.snap_id;
      Begin
          select sum(SPACE_ALLOCATED_DELTA) into v_TotalGrowth from DBA_HIST_SEG_STAT;
          select sum(bytes) into v_CurrentSize from dba_segments;
          v_BaselineSize := v_CurrentSize - v_TotalGrowth ;

          dbms_output.put_line("SNAP_TIME           Database Size(MB)");

          for row in usageHist loop
                  v_Space := (v_BaselineSize + row.ProgSum)/(1024*1024);
              dbms_output.put_line(row.SNAP_TIME || "           " || to_char(v_Space) );
          end loop;
      end;

@Oracle growth report at the table level
set feedback off
set pages 80
set linesize 150
spool /tmp/weekly_growth.txt
 
ttitle "Total Disk Used"
 
select sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || "%" "Percent of Total Disk Usage"
from 
   dba_hist_snapshot sn, 
   dba_hist_seg_stat a, 
   dba_objects b, 
   dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner = "&schema_name"
and space_used_delta > 0;
 
ttitle "Total Disk Used by Object Type"
 
select c.segment_type, sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Space (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || "%" "Percent of Total Disk Usage"
from 
   dba_hist_snapshot sn, 
   dba_hist_seg_stat a, 
   dba_objects b, 
   dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and space_used_delta > 0
and c.owner = "&schema_name"
group by rollup(segment_type);
spool off

代码100分@top_grow_seg
select
so.owner,
so.object_name,
--so.subobject_name,
so.object_type,
so.tablespace_name,
round(sum(ss.space_used_delta)/1024/1024) growth_mb
from
dba_hist_seg_stat ss,
dba_hist_seg_stat_obj so
where
ss.obj# = so.obj#
and ss.dataobj# = so.dataobj#
and so.owner != "** MISSING **" -- segments already gone
and so.object_name not like "BIN$%" -- recycle-bin
and so.object_type not like "LOB%"
and ss.snap_id > (
select min(sn.snap_id)
from dba_hist_snapshot sn
where
sn.dbid = (select dbid from v$database)
and sn.end_interval_time > trunc(sysdate) - &DAYS_BACK
)
group by
so.owner,
so.object_name,
--so.subobject_name,
so.object_type,
so.tablespace_name
order by 5 desc
fetch first &TOP rows only;