Schedule shell script for tablespace report in oracle database

by

Schedule shell script for tablespace report in oracle database| In this article we will explain ‘how to generate Tablespace report’ in oracle database and automate alert on mail using mailx.

Check tablespace size in oracle database

To pull out tablespace report in oracle database. We will prepare the shell script as below and schedule it using crontab.

Step 1:- Prepare tablespace script

copy the below scripts in shell scripts as tablespacerpt.sh and grant the permission to execute shell script.

$chmod a+x tablespacerpt.sh

#!/sbin/sh
ORACLE_SID=ORCL; export ORACLE_SID
. /oracle/home/ora10g/ORCL.env

/oracle/home/ora10g/ora10g/bin/sqlplus -S -M "HTML ON TABLE 'BORDER="8"'" user/password @/oracle/home/ora10g/tablespacereport.sql

cat /oracle/home/ora10g/mailheader.log TABLESPACE_REPORT.html | mailx -t;

———————–

SET SERVEROUTPUT ON SIZE 1000000;
SET LINESIZE 9999;
set pagesize 400;
COLUMN TABLESPACE HEADING 'TABLESPACE' ENTMAP OFF
COLUMN TOTAL_SIZE(MB) HEADING 'TOTAL_SIZE(MB)'
COLUMN USED_SIZE(MB)  HEADING 'USED_SIZE(MB)'
COLUMN LARGEST_EXTENTS HEADING 'LARGEST_EXTENTS'
COLUMN FREE_SIZE(MB) HEADING 'FREE_SIZE(MB)'
COLUMN USED_USAGE HEADING 'USED_USAGE'
COLUMN FREE_USAGE HEADING 'FREE_USAGE'

SPOOL ABLESPACE_REPORT.html

select substr(A.tablespace_name,1,16) "TABLESPACE",SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024
"TOTAL_SIZE(MB)",(SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024)-(ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)))
"USED_SIZE(MB)",ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)) "FREE_SIZE(MB)",MAX(A.max_extents)
"LARGEST_EXTENTS",TO_CHAR(100-(SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%'
"USED_USAGE",
TO_CHAR((SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "FREE_USAGE"
from dba_tablespaces A,DBA_DATA_FILES B,DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.TABLESPACE_NAME=C.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME
order by 6 desc;

SPOOL OFF
exit:

Step 2:- Prepare mailheader

In this step we will create the mailheader file to send tablespace report on email.

Copy the below content in file name mailheader.log

From:[email protected]
To:[email protected]
Cc:
Subject:Tablespace Report
Content-Type: text/html

After that schedule it using crontab as below.

Edit crontab :- $crontab -e

00 04 * * * /oracle/home/ora10g/tablespacerpt.sh

save it by press Esc + : then wq!.

Leave a Comment