Send TABLESPACE status report mail in HTML Format
Step 1 : Create SQL script file
To Generate HTML file for database status and tablespace report
Step 2 : Spool to shell script file
Step 3 : execute shell script to get tablespace report via mail in HTML format
Step 1 and Step 2
[oracle]$ cat report.sql
SET head OFF;
SET echo OFF;
SET termout OFF;
SET verify OFF;
set colsep ,
set pagesize 0
set feedback off
spool /home/oracle/Desktop/dilip/MAIL_SCRIPT/test_report.sh
select 'sendmail sample_dbmonitoring@xyz.com <<EOF' from dual;
select 'To: dbmonitoring_group@xyz.com ' from dual;
select 'Subject: Text message' from dual;
select 'Content-Type: text/html; charset="us-ascii"' from dual;
select '<html>' from dual;
select '<body>' from dual;
select '<p>' from dual;
select '<font size="5" face="Courier" color="blue">' from dual;
select 'Tablespace Alert' from dual;
select '</font>' from dual;
select '<font size="2" face="screen" color="green">' from dual;
spool off
SET head on;
set heading on;
set verify on;
set termout on
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>DATABASE NAME and STATUS </TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: white} --> -
</STYLE>" -
BODY "TEXT=blue" -
TABLE "WIDTH='50%' BORDER='5'"
set pagesize 200
spool /home/oracle/Desktop/dilip/MAIL_SCRIPT/test_report.sh append;
prompt 1.DATABASE STATUS
select name as DBNAME,open_mode,database_role,(select startup_time from v$instance) as starttime from v$database;
prompt 2.TABLESPACE STATUS
select a.tablespace_name as "TSNAME",
round((b.totalspace - a.freespace),1)"USED_SPACE_MB",
round(a.freespace,1) "FREE_SPACE_MB",
round(b.totalspace) "TOTAL_SPACE_MB",
round(100 * (a.freespace / b.totalspace),5) "%_FREE"
from
( select tablespace_name,
sum(bytes)/1024/1024 TotalSpace
from dba_data_files
group by tablespace_name) b,
( select tablespace_name,
sum(bytes)/1024/1024 FreeSpace
from dba_free_space
group by tablespace_name) a
where b.tablespace_name = a.tablespace_name(+)
order by 5;
spool off
set markup html off
set heading off
set heading off
SET head OFF;
SET echo OFF;
SET termout OFF;
SET verify OFF;
set colsep ,
set pagesize 0
spool /home/oracle/Desktop/dilip/MAIL_SCRIPT/test_report.sh append;
select '</font>' from dual;
select '</p>' from dual;
select '</body>' from dual;
select '</html>' from dual;
prompt EOF
spool off
exit
--Generated shell script file which will send mail in HTML format of tablespace status
Verify the generated file
[oracle]$ cat test_report.sh
sendmail sample_dbmonitoring@xyz.com <<EOF
To: dbmonitoring_group@xyz.com
Subject: Text message
Content-Type: text/html; charset="us-ascii"
<html>
<body>
<p>
<font size="5" face="Courier" color="blue">
Tablespace Alert
</font>
<font size="2" face="screen" color="green">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">
<meta name="generator" content="SQL*Plus 11.2.0">
<TITLE>DATABASE NAME and STATUS </TITLE> <STYLE type='text/css'> <!-- BODY {background: white} --> </STYLE>
</head>
<body TEXT=blue>
1.DATABASE STATUS
<br>
<p>
<table WIDTH='50%' BORDER='5'>
<tr>
<th scope="col">
DBNAME
</th>
<th scope="col">
OPEN_MODE
</th>
<th scope="col">
DATABASE_ROLE
</th>
<th scope="col">
STARTTIME
</th>
</tr>
<tr>
<td>
ORACLE
</td>
<td>
READ WRITE
</td>
<td>
PRIMARY
</td>
<td>
25-MAR-21
</td>
</tr>
</table>
<p>
2.TABLESPACE STATUS
<br>
<p>
<table WIDTH='50%' BORDER='5'>
<tr>
<th scope="col">
TSNAME
</th>
<th scope="col">
USED_SPACE_MB
</th>
<th scope="col">
FREE_SPACE_MB
</th>
<th scope="col">
TOTAL_SPACE_MB
</th>
<th scope="col">
%_FREE
</th>
</tr>
<tr>
<td>
SYSTEM
</td>
<td align="right">
694.6
</td>
<td align="right">
5.4
</td>
<td align="right">
700
</td>
<td align="right">
.76786
</td>
</tr>
<tr>
<td>
SYSAUX
</td>
<td align="right">
706.4
</td>
<td align="right">
53.6
</td>
<td align="right">
760
</td>
<td align="right">
7.0477
</td>
</tr>
<tr>
<td>
USERS
</td>
<td align="right">
2.4
</td>
<td align="right">
2.6
</td>
<td align="right">
5
</td>
<td align="right">
51.25
</td>
</tr>
<tr>
<td>
UNDOTBS1
</td>
<td align="right">
11.3
</td>
<td align="right">
303.8
</td>
<td align="right">
315
</td>
<td align="right">
96.42857
</td>
</tr>
<tr>
<td>
TRAING
</td>
<td align="right">
2.2
</td>
<td align="right">
5122.8
</td>
<td align="right">
5125
</td>
<td align="right">
99.95732
</td>
</tr>
</table>
<p>
</body>
</html>
</font>
</p>
</body>
</html>
EOF
Content-Type: text/html; charset="us-ascii"
<html>
<body>
<p>
<font size="5" face="Courier" color="blue">
Tablespace Alert
</font>
<font size="2" face="screen" color="green">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">
<meta name="generator" content="SQL*Plus 11.2.0">
<TITLE>DATABASE NAME and STATUS </TITLE> <STYLE type='text/css'> <!-- BODY {background: white} --> </STYLE>
</head>
<body TEXT=blue>
1.DATABASE STATUS
<br>
<p>
<table WIDTH='50%' BORDER='5'>
<tr>
<th scope="col">
DBNAME
</th>
<th scope="col">
OPEN_MODE
</th>
<th scope="col">
DATABASE_ROLE
</th>
<th scope="col">
STARTTIME
</th>
</tr>
<tr>
<td>
ORACLE
</td>
<td>
READ WRITE
</td>
<td>
PRIMARY
</td>
<td>
25-MAR-21
</td>
</tr>
</table>
<p>
2.TABLESPACE STATUS
<br>
<p>
<table WIDTH='50%' BORDER='5'>
<tr>
<th scope="col">
TSNAME
</th>
<th scope="col">
USED_SPACE_MB
</th>
<th scope="col">
FREE_SPACE_MB
</th>
<th scope="col">
TOTAL_SPACE_MB
</th>
<th scope="col">
%_FREE
</th>
</tr>
<tr>
<td>
SYSTEM
</td>
<td align="right">
694.6
</td>
<td align="right">
5.4
</td>
<td align="right">
700
</td>
<td align="right">
.76786
</td>
</tr>
<tr>
<td>
SYSAUX
</td>
<td align="right">
706.4
</td>
<td align="right">
53.6
</td>
<td align="right">
760
</td>
<td align="right">
7.0477
</td>
</tr>
<tr>
<td>
USERS
</td>
<td align="right">
2.4
</td>
<td align="right">
2.6
</td>
<td align="right">
5
</td>
<td align="right">
51.25
</td>
</tr>
<tr>
<td>
UNDOTBS1
</td>
<td align="right">
11.3
</td>
<td align="right">
303.8
</td>
<td align="right">
315
</td>
<td align="right">
96.42857
</td>
</tr>
<tr>
<td>
TRAING
</td>
<td align="right">
2.2
</td>
<td align="right">
5122.8
</td>
<td align="right">
5125
</td>
<td align="right">
99.95732
</td>
</tr>
</table>
<p>
</body>
</html>
</font>
</p>
</body>
</html>
EOF
Step 3 : Execute the shell script
[oracle]$./test_report.sh