question Oracle’da hangi tablo büyüdü?

Veritabanında son bir kaç gündür tablespace belirgin şekilde büyümüş. Hangi tablonun bu büyümeye sebep olduğunu nasıl öğrenebilirim?

comments 1 references

// @author akotako | 13 Mayıs 2017

Selamlar,

Aşağıdaki soruguyu kullanarak ortalama büyüme oranlarını elde edebilirsiniz.

SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,

    t.NAME “Tablespace Name”, s.growth/(1024*1024) “Growth in MB”,

    (SELECT sum(bytes)/(1024*1024)

    FROM dba_segments

    WHERE segment_name=o.object_name) “Total Size(MB)”

FROM DBA_OBJECTS o,

    ( SELECT TS#,OBJ#,

        SUM(SPACE_USED_DELTA) growth

    FROM DBA_HIST_SEG_STAT

    GROUP BY TS#,OBJ#

    HAVING SUM(SPACE_USED_DELTA) > 0

    ORDER BY 2 DESC ) s,

    v$tablespace t

WHERE s.OBJ# = o.OBJECT_ID

AND s.TS#=t.TS#

AND rownum < 51

ORDER BY 6 DESC

Aşağıdaki script ile de DBA_HIST tabloları kullanılarak database space artışını gözlemleyebilirsiniz.

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;

// Comments are closed.