oracletable,databasegrowthreportscript
@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;
@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
@top_grow_segselect
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;
以上是 oracletable,databasegrowthreportscript 的全部内容, 来源链接: utcz.com/z/532198.html