Oracle Database Growth Monitoring

Oracle Database Growth Monitoring – Simplified Guide

This guide provides queries and scripts to monitor database growth at different levels:

1. 📅 Database Growth Per Month

SELECT
to_char(creation_time, 'MM-RRRR') AS "Month",
round(SUM(bytes) / 1024 / 1024 / 1024, 2) AS "Growth in GB"
FROM
v$datafile
WHERE
to_char(creation_time, 'RRRR') = '2023'
GROUP BY
to_char(creation_time, 'MM-RRRR')
ORDER BY
1;

2. 📦 Tablespace-Level Growth (Last 7 Days)

SELECT
b.tsname AS tablespace_name,
MAX(b.used_size_mb) AS cur_used_size_mb,
round(AVG(inc_used_size_mb), 2) AS avg_increase_mb
FROM
(
SELECT
a.days,
a.tsname,
a.used_size_mb,
a.used_size_mb - LAG(a.used_size_mb, 1)
OVER(PARTITION BY a.tsname
ORDER BY
a.tsname, a.days
) AS inc_used_size_mb
FROM
(
SELECT
to_char(sp.begin_interval_time, 'MM-DD-YYYY') AS days,
ts.tsname,
MAX(round(tsu.tablespace_usedsize * dt.block_size / 1024 / 1024, 2)) AS 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
) a
) b
GROUP BY
b.tsname
ORDER BY
b.tsname;

3. 📋 Database Size, Used/Free Space, Growth Per Day and Per Week

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;

4. 📊 Current Tablespace Size and Average Daily Growth

SELECT
b.tsname AS tablespace_name,
MAX(b.used_size_mb) AS cur_used_size_mb,
round(AVG(inc_used_size_mb), 2) AS avg_increase_mb
FROM
(
SELECT
a.days,
a.tsname,
a.used_size_mb,
a.used_size_mb - LAG(a.used_size_mb, 1)
OVER(PARTITION BY a.tsname
ORDER BY
a.tsname, a.days
) AS inc_used_size_mb
FROM
(
SELECT
to_char(sp.begin_interval_time, 'MM-DD-YYYY') AS days,
ts.tsname,
MAX(round(tsu.tablespace_usedsize * dt.block_size / 1024 / 1024, 2)) AS 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
) a
) b
GROUP BY
b.tsname
ORDER BY
b.tsname;

5. 📅 Monthly 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;

6. 🗓️ Simple Database Growth Per Month

COLUMN month FORMAT a20 COLUMN growth FORMAT 999,999,999,999,999 SELECT TO_CHAR(creation_time, 'RRRR-MM') AS "Month", SUM(bytes/1024/1024) AS "Growth (MB)" FROM sys.v_$datafile GROUP BY TO_CHAR(creation_time, 'RRRR-MM') ORDER BY 1;

7. 📈 DB Growth Per Tablespace, Daily and Weekly

SELECT
MIN(creation_time) AS "Create Time",
ts.name,
round(SUM(df.bytes) / 1024 / 1024) AS curr_size_mb,
round((SUM(df.bytes) / 1024 / 1024) / round(sysdate - MIN(creation_time)), 1) AS growth_per_day_mb,
round(((SUM(df.bytes) / 1024 / 1024) / round(sysdate - MIN(creation_time))) * 7, 1) AS growth_per_week_mb
FROM
v$datafile df,
v$tablespace ts
WHERE
df.ts# = ts.ts#
GROUP BY
df.ts#,
ts.name
ORDER BY
df.ts#;

8. 📅 Database Growth History and Forecast (PL/SQL Block)

▶ A script that calculates total growth and expected growth:

set serverout on
set verify off
set lines 200
set pages 2000

DECLARE
v_ts_id NUMBER;
not_in_awr EXCEPTION;
v_ts_block_size NUMBER;
v_begin_snap_id NUMBER;
v_end_snap_id NUMBER;
v_begin_snap_date DATE;
v_end_snap_date DATE;
v_numdays NUMBER;
v_count NUMBER;
v_ts_begin_size NUMBER;
v_ts_end_size NUMBER;
v_ts_growth NUMBER;
v_ts_begin_allocated_space NUMBER;
v_ts_end_allocated_space NUMBER;
v_db_begin_size NUMBER := 0;
v_db_end_size NUMBER := 0;
v_db_begin_allocated_space NUMBER := 0;
v_db_end_allocated_space NUMBER := 0;
v_db_growth NUMBER := 0;
CURSOR v_cur IS
SELECT
tablespace_name
FROM
dba_tablespaces
WHERE
contents = 'PERMANENT';

BEGIN
FOR v_rec IN v_cur LOOP
BEGIN
v_ts_begin_allocated_space := 0;
v_ts_end_allocated_space := 0;
v_ts_begin_size := 0;
v_ts_end_size := 0;
SELECT
ts#
INTO v_ts_id
FROM
v$tablespace
WHERE
name = v_rec.tablespace_name;

SELECT
block_size
INTO v_ts_block_size
FROM
dba_tablespaces
WHERE
tablespace_name = v_rec.tablespace_name;

SELECT
COUNT(*)
INTO v_count
FROM
dba_hist_tbspc_space_usage
WHERE
tablespace_id = v_ts_id;

IF v_count = 0 THEN
RAISE not_in_awr;
END IF;
SELECT
MIN(snap_id),
MAX(snap_id),
MIN(trunc(to_date(rtime, 'MM/DD/YYYY HH24:MI:SS'))),
MAX(trunc(to_date(rtime, 'MM/DD/YYYY HH24:MI:SS')))
INTO
v_begin_snap_id,
v_end_snap_id,
v_begin_snap_date,
v_end_snap_date
FROM
dba_hist_tbspc_space_usage
WHERE
tablespace_id = v_ts_id;

IF upper(v_rec.tablespace_name) = 'SYSTEM' THEN
v_numdays := v_end_snap_date - v_begin_snap_date;
END IF;

SELECT
round(MAX(tablespace_size) * v_ts_block_size / 1024 / 1024, 2)
INTO v_ts_begin_allocated_space
FROM
dba_hist_tbspc_space_usage
WHERE
tablespace_id = v_ts_id
AND snap_id = v_begin_snap_id;

SELECT
round(MAX(tablespace_size) * v_ts_block_size / 1024 / 1024, 2)
INTO v_ts_end_allocated_space
FROM
dba_hist_tbspc_space_usage
WHERE
tablespace_id = v_ts_id
AND snap_id = v_end_snap_id;

SELECT
round(MAX(tablespace_usedsize) * v_ts_block_size / 1024 / 1024, 2)
INTO v_ts_begin_size
FROM
dba_hist_tbspc_space_usage
WHERE
tablespace_id = v_ts_id
AND snap_id = v_begin_snap_id;

SELECT
round(MAX(tablespace_usedsize) * v_ts_block_size / 1024 / 1024, 2)
INTO v_ts_end_size
FROM
dba_hist_tbspc_space_usage
WHERE
tablespace_id = v_ts_id
AND snap_id = v_end_snap_id;

v_db_begin_allocated_space := v_db_begin_allocated_space + v_ts_begin_allocated_space;
v_db_end_allocated_space := v_db_end_allocated_space + v_ts_end_allocated_space;
v_db_begin_size := v_db_begin_size + v_ts_begin_size;
v_db_end_size := v_db_end_size + v_ts_end_size;
v_db_growth := v_db_end_size - v_db_begin_size;
END;
END LOOP;

dbms_output.put_line(chr(10));
dbms_output.put_line('Summary');
dbms_output.put_line('========');
dbms_output.put_line('1) Allocated Space: '
|| v_db_end_allocated_space
|| ' MB'
|| ' ('
|| round(v_db_end_allocated_space / 1024, 2)
|| ' GB)');

dbms_output.put_line('2) Used Space: '
|| v_db_end_size
|| ' MB'
|| ' ('
|| round(v_db_end_size / 1024, 2)
|| ' GB)');

dbms_output.put_line('3) Used Space Percentage: '
|| round(v_db_end_size / v_db_end_allocated_space * 100, 2)
|| ' %');

dbms_output.put_line(chr(10));
dbms_output.put_line('History');
dbms_output.put_line('========');
dbms_output.put_line('1) Allocated Space on '
|| v_begin_snap_date
|| ': '
|| v_db_begin_allocated_space
|| ' MB'
|| ' ('
|| round(v_db_begin_allocated_space / 1024, 2)
|| ' GB)');

dbms_output.put_line('2) Current Allocated Space on '
|| v_end_snap_date
|| ': '
|| v_db_end_allocated_space
|| ' MB'
|| ' ('
|| round(v_db_end_allocated_space / 1024, 2)
|| ' GB)');

dbms_output.put_line('3) Used Space on '
|| v_begin_snap_date
|| ': '
|| v_db_begin_size
|| ' MB'
|| ' ('
|| round(v_db_begin_size / 1024, 2)
|| ' GB)');

dbms_output.put_line('4) Current Used Space on '
|| v_end_snap_date
|| ': '
|| v_db_end_size
|| ' MB'
|| ' ('
|| round(v_db_end_size / 1024, 2)
|| ' GB)');

dbms_output.put_line('5) Total growth during last '
|| v_numdays
|| ' days between '
|| v_begin_snap_date
|| ' and '
|| v_end_snap_date
|| ': '
|| v_db_growth
|| ' MB'
|| ' ('
|| round(v_db_growth / 1024, 2)
|| ' GB)');

IF ( v_db_growth <= 0 OR v_numdays <= 0 ) THEN
dbms_output.put_line(chr(10));
dbms_output.put_line('No data growth was found for the Database');
ELSE
dbms_output.put_line('6) Per day growth during last '
|| v_numdays
|| ' days: '
|| round(v_db_growth / v_numdays, 2)
|| ' MB'
|| ' ('
|| round((v_db_growth / v_numdays) / 1024, 2)
|| ' GB)');

dbms_output.put_line(chr(10));
dbms_output.put_line('Expected Growth');
dbms_output.put_line('===============');
dbms_output.put_line('1) Expected growth for next 30 days: '
|| round((v_db_growth / v_numdays) * 30, 2)
|| ' MB'
|| ' ('
|| round(((v_db_growth / v_numdays) * 30) / 1024, 2)
|| ' GB)');

dbms_output.put_line('2) Expected growth for next 60 days: '
|| round((v_db_growth / v_numdays) * 60, 2)
|| ' MB'
|| ' ('
|| round(((v_db_growth / v_numdays) * 60) / 1024, 2)
|| ' GB)');

dbms_output.put_line('3) Expected growth for next 90 days: '
|| round((v_db_growth / v_numdays) * 90, 2)
|| ' MB'
|| ' ('
|| round(((v_db_growth / v_numdays) * 90) / 1024, 2)
|| ' GB)');

dbms_output.put_line(chr(10));
dbms_output.put_line('/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\');
END IF;

EXCEPTION
WHEN not_in_awr THEN
dbms_output.put_line(chr(10));
dbms_output.put_line('====================================================================================================================');
dbms_output.put_line('!!! ONE OR MORE TABLESPACES USAGE INFORMATION NOT FOUND IN AWR !!!');
dbms_output.put_line('Execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT, or wait for next AWR snapshot capture before executing this script');
dbms_output.put_line('====================================================================================================================');
END;
/
/############################################# #