Database Growth Usages Stats
- From: Sheldon Quinny <sheldonquinny@xxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 19 Feb 2012 12:05:35 +0300
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
