In this article we will explain ‘how to generate Tablespace reprot’ in oracle database and automate alert on mail using mailx.
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!.