Oracle script to check the database growth
SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
FROM (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
Output-
The below script lists the details of database growth per month:
select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v_$datafile
where to_char(creation_time,'RRRR')='2014'
group by to_char(creation_time, 'MM-RRRR')
order by to_char(creation_time, 'MM-RRRR');
from sys.v_$datafile
where to_char(creation_time,'RRRR')='2014'
group by to_char(creation_time, 'MM-RRRR')
order by to_char(creation_time, 'MM-RRRR');
Sample output from the script:
Month Growth in GB
------------------------------------------------------------ -----------------
05-2014 101.588867
06-2014 525.609375
07-2014 57.5
09-2014 10
10-2014 31.0976563
11-2014 52
Month Growth in GB
------------------------------------------------------------ -----------------
05-2014 101.588867
06-2014 525.609375
07-2014 57.5
09-2014 10
10-2014 31.0976563
11-2014 52
Below script is useful for tablespace level database growth:
select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb
from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_tbspc_space_usage tsu , dba_hist_tablespace_stat ts ,dba_hist_snapshot sp, dba_tablespaces dt where tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days ) a ) b GROUP BY b.tsname ORDER BY b.tsname;
Sample output from the script:
TABLESPACE_NAME CUR_USED_SIZE_MB AVG_INCREAS_MB
--------------------------------- --------------------------- -------------------------
DW_AGG 8150.31 46.94
DW_DAC_REP_DATA 858.31 3.29
DW_DIM_DATA 7078.81 6
DW_DIM_INDX 3229.69 3.89
DW_DIM_STG 1202.38 -9.61
DW_FACT_DATA 304706.94 894.51
DW_FACT_INDX 32227.81 183.04
DW_FACT_STG 5483.81 120.7
DW_INFA_DOMAIN_DATA 1 0
DW_INFA_REP_DATA 3617.88 2.43
DW_OTHER 14314.88 83.68
DW_OTHER_INDX 203.81 .71
PRD_BIPLATFORM 7493 64.57
PRD_MDS 13.44 0
SYSAUX 7097.75 -3.94
SYSTEM 2408.31 10.43
UNDOTBS1 62488.13 7074.66
UNDOTBS2 13088.38 -866.43
UNDOTBS3 81902 10326
USERS 2015.13 -2.68
Tracking Oracle database growth:
select to_char(CREATION_TIME,'RRRR') year,to_char(CREATION_TIME,'MM') month,round(sum(bytes)/1024/1024/1024) gb
from v$datafile group by to_char(CREATION_TIME,'RRRR'),to_char(CREATION_TIME,'MM') order by 1,2;
from v$datafile group by to_char(CREATION_TIME,'RRRR'),to_char(CREATION_TIME,'MM') order by 1,2;
YEAR MO GB
---- -- ----------
2000 04 9
2004 06 5
2004 11 5
2005 01 2
2005 02 4
2005 06 4
2005 09 20
2005 10 2
2005 11 12
2006 01 8
2007 01 1
2007 06 8
2007 08 1490
2008 01 8
2008 02 9
2008 03 44
---- -- ----------
2000 04 9
2004 06 5
2004 11 5
2005 01 2
2005 02 4
2005 06 4
2005 09 20
2005 10 2
2005 11 12
2006 01 8
2007 01 1
2007 06 8
2007 08 1490
2008 01 8
2008 02 9
2008 03 44
Excellent.. Very informative and Helpful.
ReplyDeleteVery helpful and informative read
ReplyDeleteThanks,
ReplyDelete