crontab task to collect Database stats and log to file

crontab
7 6 1 * * /software/oracle/oracle/scripts/run_collect_stats.sh



run_collect_stats.sh
#!/bin/bash

. /etc/sh/orash/oracle_login.sh igt

LOG_FILE=/software/oracle/oracle/scripts/history_stats/history_stats.log
WORK_DIR=/software/oracle/oracle/scripts
HIST_DIR=/software/oracle/oracle/scripts/history_stats

cd ${WORK_DIR}

sqlplus system/Xen86Pga@igt @tbs.sql
sqlplus system/Xen86Pga@igt @segments.sql
sqlplus system/Xen86Pga@igt @files.sql

RUN_DATE=`date +"%Y%m%d_%H%M%S"`
touch $LOG_FILE

echo "===================================" >> $LOG_FILE
echo "Run Date: $RUN_DATE" >>  $LOG_FILE
echo "===================================" >>  $LOG_FILE
mv -f ${WORK_DIR}/tbs.txt ${HIST_DIR}/tbs_${RUN_DATE}.txt
mv -f ${WORK_DIR}/segments.txt ${HIST_DIR}/segments_${RUN_DATE}.txt
mv -f ${WORK_DIR}/datafiles.txt ${HIST_DIR}/datafiles_${RUN_DATE}.txt

chmod 666 ${HIST_DIR}/tbs_${RUN_DATE}.txt
chmod 666 ${HIST_DIR}/segments_${RUN_DATE}.txt
chmod 666 ${HIST_DIR}/datafiles_${RUN_DATE}.txt

tbs.sql

SET LINESIZE 120
SET PAGESIZE 200
spool tbs.txt

SELECT TABLESPACE_NAME,
          (MAX(MAX_SPACE)-MAX(USED_SPACE)  )AS DBA_FREE_SPACE_MB,
          MAX(USED_SPACE) AS USED_SPACE_MB,
          MAX(MAX_SPACE) AS MAX_SPACE_MB,
          ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,
          CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100)<15)  THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE
FROM (
SELECT tablespace_name,
           ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,
           0 AS MAX_SPACE,
           0 AS USED_SPACE
 FROM DBA_FREE_SPACE
 WHERE 1=1
   --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX'
 GROUP BY tablespace_name
 UNION ALL
 SELECT tablespace_name,
           0 AS FREE_SPACE,
           ROUND( SUM(CASE WHEN (bytes>maxbytes)  THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE,
     0 AS USED_SPACE
 FROM DBA_DATA_FILES
  WHERE 1=1
   --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX'
 GROUP BY tablespace_name
 UNION ALL
 SELECT tablespace_name,
     0 AS FREE_SPACE,
     0 AS MAX_SPACE,
     ROUND(SUM(bytes/1024/1024)) AS USED_SPACE
  FROM DBA_SEGMENTS
 WHERE 1=1
   --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX'
GROUP BY tablespace_name
)
GROUP BY tablespace_name;

spool off
EXIT;


segments.sql
SET LINESIZE 120
SET PAGESIZE 200
COL tablespace_name FOR A30
COL owner FOR A30
COL segment_name FOR A30
COL USED_MB FOR 9999999999999

spool segments.txt

SELECT owner, tablespace_name, segment_name, USED_MB
   FROM (
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%TABLE%'
  GROUP BY owner, tablespace_name,segment_name
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 11;


SELECT owner, tablespace_name, segment_name, USED_MB
   FROM (
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%INDEX%'
  GROUP BY owner, tablespace_name,segment_name
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 11;

SELECT owner, tablespace_name, segment_name, USED_MB
   FROM (
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%SYSTEM%'
  GROUP BY owner, tablespace_name,segment_name
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 11;


SELECT owner, tablespace_name, segment_name, USED_MB
   FROM (
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%SYSAUX%'
  GROUP BY owner, tablespace_name,segment_name
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 11;

spool off
EXIT;


files.sql
SET LINESIZE 140
SET PAGESIZE 200
COL tablespace_name FOR A30
COL file_name FOR A60
COL Mb FOR 9999999999999
COL MAX_MB FOR 9999999999999

spool datafiles.txt
SELECT tablespace_name, file_name, ROUND(bytes/1024/1024) AS Mb, ROUND(maxbytes/1024/1024) AS MAX_MB
  FROM DBA_DATA_FILES
ORDER BY tablespace_name, file_name;
spool off

EXIT;