Collect hourly segment growth report in Oracle Database

You can call below described .sql file via shell script. Schedule that .sh file in crontab to collect the metrics.


1) Create .sh file to call .sql file

$ cat /u01/app/oracle/admin/testdb/scripts/schema_segment_growth_report.sh

#!/bin/bash
export DT=`date +%d%m%Y_%H%M%S`
export ORACLE_SID=testdb
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.2/dbhome_1
export SCRIPT_HOME=/u01/app/oracle/admin/testdb/scripts
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin
cd /u03/Admin_Reports/Segment_Stats
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' << EOF
whenever sqlerror exit sql.sqlcode;
set echo off
@$SCRIPT_HOME/segment_growth_report.sql
exit;
EOF


2) Create the .sql file to collect the report

$ cat segment_growth_report.sql

set linesize 122
spool segment_growth_report_${ORACLE_SID}_${DT}.log
set feedback off
col owner for a30
col segment_name for a30
col segment_type for a22
select * from (
select owner, segment_name, segment_type,round(bytes/1024/1024,1) Size_Mb from dba_segments
where owner not in('SYS','SYSTEM') order by
bytes/1024/1024  DESC ) where rownum <= 10;

spool off

exit


3) Schedule in Cron

00 * * * * /u01/app/oracle/admin/testdb/scripts/schema_segment_growth_report.sh   1>/dev/null 2>&1