Here is a quick script that will help monitor the:
Oracle Database Growth
Tablespace Growth
Datafiles Growth
#!/bin/bash
# Set up environment variables
export ORACLE_HOME=<oracle_home>
export ORACLE_SID=<oracle_sid>
# Set up directory for log file
LOG_DIR=<log_directory>
DAILY_LOG=$LOG_DIR/db_growth.log
# Set up email variables
EMAIL_FROM=<email_from>
EMAIL_TO=<email_to>
# Set up database connection variables
DB_USERNAME=<username>
DB_PASSWORD=<password>
# Function to get database growth
get_database_growth() {
sqlplus -s $DB_USERNAME/$DB_PASSWORD <<EOF
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;
EOF
}
# Function to get tablespace growth
get_tablespace_growth() {
sqlplus -s $DB_USERNAME/$DB_PASSWORD <<EOF
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;
EOF
}
# Function to get datafiles growth
get_datafiles_growth() {
sqlplus -s $DB_USERNAME/$DB_PASSWORD <<EOF
SELECT TO_CHAR (creation_time, 'MM-RRRR')"Month", SUM (bytes) / 1024 / 1024 / 1024 "Growth in GB"
FROM sys.v_$datafile GROUP BY TO_CHAR (creation_time, 'MM-RRRR')
ORDER BY TO_CHAR (creation_time, 'MM-RRRR');
EOF
}
# Daily monitoring
echo "Daily monitoring" > $DAILY_LOG
echo "----------------" >> $DAILY_LOG
echo >> $DAILY_LOG
echo "Database growth:" >> $DAILY_LOG
echo "----------------" >> $DAILY_LOG
get_database_growth >> $DAILY_LOG
echo >> $DAILY_LOG
echo "Tablespace growth:" >> $DAILY_LOG
echo "------------------" >> $DAILY_LOG
get_tablespace_growth >> $DAILY_LOG
echo >> $DAILY_LOG
echo "Datafiles growth:" >> $DAILY_LOG
echo "-----------------" >> $DAILY_LOG
get_datafiles_growth >> $DAILY_LOG
# Send email notification
mailx -s "Oracle database growth - Daily report" -r $EMAIL_FROM $EMAIL_TO < $DAILY_LOG