Schedule shell script for tablespace report 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:-

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:- 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!.