Re: Database Growth Usages Stats

From: Lei Zeng <leizeng2003_at_yahoo.com>
Date: Sun, 19 Feb 2012 19:26:13 -0800 (PST)
Message-ID: <1329708373.14377.YahooMailNeo_at_web114706.mail.gq1.yahoo.com>

Sheldon:
 You can directly query DBA_HIST_TBSPC_SPACE_USAGE and summarize space usage from tablespace level to database level.  
AWR automatically keeps track of space usage information at certain levels. The following query is a sample from AWR data mining tool: DBspeed  ( “AWR Storage Statistics" -> "AWR database size – by day” )  
WITH x AS (

    SELECT DBID, SUBSTR(RTIME, 1,10) day, MIN(SNAP_ID) snap_id
    FROM DBA_HIST_TBSPC_SPACE_USAGE
    WHERE DBID=? AND SNAP_ID BETWEEN ? AND ?
    GROUP BY DBID, SUBSTR(RTIME, 1,10)
 ),
 y AS (
    SELECT DBID, SNAP_ID, TS#, TSNAME, MIN(BLOCK_SIZE) block_size
    FROM DBA_HIST_FILESTATXS
    WHERE DBID=? AND SNAP_ID IN (SELECT snap_id from x)
    GROUP BY DBID, SNAP_ID, TS#, TSNAME
    UNION
    SELECT DBID, SNAP_ID, TS#, TSNAME, MIN(BLOCK_SIZE) block_size
    FROM DBA_HIST_TEMPSTATXS
    WHERE DBID=? AND SNAP_ID IN (SELECT snap_id from x)
    GROUP BY DBID, SNAP_ID, TS#, TSNAME
 )
 SELECT z.SNAP_ID, z. RTIME,
 SUM(ROUND(z.TABLESPACE_SIZE*y.block_size/1024/1024)) current_MB,
 SUM(ROUND(z.TABLESPACE_MAXSIZE*y.block_size/1024/1024)) max_MB,
 SUM(ROUND(z.TABLESPACE_USEDSIZE*y.block_size/1024/1024)) used_MB,
 ROUND(SUM(z.TABLESPACE_MAXSIZE-z.TABLESPACE_USEDSIZE)*100/SUM(z.TABLESPACE_MAXSIZE)) free_pct
 FROM DBA_HIST_TBSPC_SPACE_USAGE z JOIN y ON (y.dbid=z.DBID AND y.snap_id=z.SNAP_ID AND y.ts#=z.TABLESPACE_ID)
 GROUP BY z.SNAP_ID, z. RTIME
 ORDER BY z.SNAP_ID
 

________________________________

From: Sheldon Quinny <sheldonquinny_at_gmail.com> To: Oracle-L Freelists <oracle-l_at_freelists.org> Sent: Sunday, February 19, 2012 1:05 AM
Subject: Database Growth Usages Stats

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

Lei
DBspeed  http://www.dbspeed.com/index.html

--

http://www.freelists.org/webpage/oracle-l Received on Sun Feb 19 2012 - 21:26:13 CST