Query to find out the Oracle Database size:
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;
Example output :
Create Ti Database Name Database Size Used Space Used in % Free Space Free in % Growth DAY Growth DAY in % Growth WEEK Growth WEEK in %
--------- ------------- ------------- ----------- ----------- ------------ ----------- ----------- ---------------- ------------ ----------------
06/DEC/10 xxxxxxxx 11645818.5 MB 2996866.5 MB 25.73% MB 8648951.56 MB 74.27% MB 8075.16 MB .069% MB 56526.13 MB .485% MB
To check database growth in months:
SELECT TO_CHAR(creation_time, 'YYYY-MM') AS month,
SUM(bytes) / 1024 / 1024 / 1024 AS growth_in_gb
FROM dba_segments
WHERE creation_time >= TRUNC(SYSDATE, 'MM')
GROUP BY TO_CHAR(creation_time, 'YYYY-MM')
ORDER BY month;
SET FEEDBACK ON
select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024/1024 "Growth in GBs"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month');
Query to find tablespace growth in last week:
select b.tsname tablespace_name , MAX(b.used_size_Gb) cur_used_size_Gb , round(AVG(inc_used_size_Gb),2)avg_increas_Gb
from ( SELECT a.days,a.tsname , used_size_Gb , used_size_Gb - LAG (used_size_Gb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_Gb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024*1024),2)) used_size_Gb
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;
Enjoy the work and do like me if you like this article!!!