Database Growth Usages Stats

From: Sheldon Quinny <sheldonquinny_at_gmail.com>
Date: Sun, 19 Feb 2012 12:05:35 +0300
Message-ID: <CAPfA+9N36P=YEKJ08JZuvoAk6vgL9mdWPOf4Zvq-FgxS5oGkfw_at_mail.gmail.com>

Hi,
I would like to know if this query is justifiable for giving me a daily report on the database use space and how much it has increased.

I am trying to use this as my Database Growth Usages Stats.

Would appreciation your wise comments and alternative suggestions

CREATE TABLE DB_GROWTH
(DAY DATE,
DATABASE_SIZE_MB NUMBER,
DAILY_GROWTH_MB NUMBER); create or replace PROCEDURE database_growth AS
today_size NUMBER;
yesterday_size NUMBER;
growth_size NUMBER;
cnt NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024) INTO today_size FROM sys.sm$ts_used; SELECT COUNT(1) INTO cnt FROM db_growth ; IF cnt > 0
THEN
SELECT database_size_mb INTO yesterday_size FROM db_growth WHERE to_date(day,'dd-mon-yy')=to_date(SYSDATE -1,'dd-mon-yy'); ELSE
yesterday_size:=today_size;
END IF;
growth_size := today_size - yesterday_size; INSERT INTO db_growth VALUES(sysdate,today_size,growth_size); EXCEPTION
WHEN no_data_found THEN
INSERT INTO db_growth VALUES(sysdate,today_size,0); DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

My Output --------------------

select DAY,DATABASE_SIZE_MB,DAILY_GROWTH_MB from db_growth order by 1;

DAY DATABASE_SIZE_MB DAILY_GROWTH_MB

--------- ---------------- ---------------
16-FEB-12       42585.9375               0
17-FEB-12       42597.0625          11.125
18-FEB-12         42594.75         -2.3125
19-FEB-12       42587.9375         -6.8125

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Feb 19 2012 - 03:05:35 CST