oracletable,databasegrowthreportscript

database

@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_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;

 

以上是 oracletable,databasegrowthreportscript 的全部内容, 来源链接: utcz.com/z/532198.html

回到顶部