OEM TS Report with HTML code

OEM TS Report with HTML code


set line 300 pages 3000
set colsep ";"
set linesize 9999
set trimspool on
set pagesize 0
set feedback off
set newpage 0
set echo off
set verify off
set arraysize 5000
spool OEM_RT.html
select
'<FONT FACE="VERDANA" SIZE=2.5 COLOR="#000000">
<table border=1 cellpadding=2 cellspacing=1>
<th bgcolor="#000951"><FONT FACE="VERDANA" SIZE=2.5 COLOR="#FFFFFF">HOST_NAME
<th bgcolor="#000951"><FONT FACE="VERDANA" SIZE=2.5 COLOR="#FFFFFF">TARGET_NAME
<th bgcolor="#000951"><FONT FACE="VERDANA" SIZE=2.5 COLOR="#FFFFFF">TARGET_TYPE
<th bgcolor="#000951"><FONT FACE="VERDANA" SIZE=2.5 COLOR="#FFFFFF">TARGET_GUID
<th bgcolor="#000951"><FONT FACE="VERDANA" SIZE=2.5 COLOR="#FFFFFF">TABLESPACE_NAME
<th bgcolor="#000951"><FONT FACE="VERDANA" SIZE=2.5 COLOR="#FFFFFF">TBS_SIZE
<th bgcolor="#000951"><FONT FACE="VERDANA" SIZE=2.5 COLOR="#FFFFFF">TBS_USED_SIZE
<th bgcolor="#000951"><FONT FACE="VERDANA" SIZE=2.5 COLOR="#FFFFFF">PCT_FREE
<th bgcolor="#000951"><FONT FACE="VERDANA" SIZE=2.5 COLOR="#FFFFFF">NAME_STATUS' from dual;
select case
when am.NAME_STATUS='VeryCritical'
then '<tr bgcolor=red>'
when am.NAME_STATUS='NeedAction'
then '<tr bgcolor=yellow>'
when am.NAME_STATUS='UnderMonitoring'
then '<tr bgcolor=green>'
else '<tr bgcolor="#amber">' end ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.HOST_NAME ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.TARGET_NAME ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.TARGET_TYPE ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.TARGET_GUID  ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.TABLESPACE_NAME ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.TBS_SIZE  ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.TBS_USED_SIZE ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am."PCT_FREE" ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.NAME_STATUS
from
(
select p.HOST_NAME as HOST_NAME ,p.TARGET_NAME as TARGET_NAME ,p.TARGET_TYPE as TARGET_TYPE,p.TARGET_GUID  as TARGET_GUID, p.TABLESPACE_NAME as TABLESPACE_NAME,p.TBS_SIZE as TBS_SIZE,p.TBS_USED_SIZE as TBS_USED_SIZE,p.PCT_FREE as PCT_FREE,case when p.PCT_FREE > 10 and p.PCT_FREE <= 20 then 'NeedAction' when p.PCT_FREE >= 20 and p.PCT_FREE < 50 then 'UnderMon' when p.PCT_FREE <= 10  then 'VeryCritical'
else 'NO_Action_Required' END as NAME_STATUS
from
(select
HOST_NAME,
TARGET_NAME,
TARGET_TYPE,
TARGET_GUID,
TABLESPACE_NAME,
TABLESPACE_SIZE/1024/1024/1024 TBS_SIZE,
TABLESPACE_USED_SIZE/1024/1024/1024 TBS_USED_SIZE,
round(nvl((TABLESPACE_SIZE-TABLESPACE_USED_SIZE),0)*100/TABLESPACE_SIZE) PCT_FREE
from mgmt$db_tablespaces
where round(nvl((TABLESPACE_SIZE-TABLESPACE_USED_SIZE),0)*100/TABLESPACE_SIZE) < 30 and TABLESPACE_SIZE >0
and TARGET_TYPE='rac_database' order by 2,5,7) p
) am  order by am.PCT_FREE;
select '<FONT FACE="VERDANA" COLOR="#E5E5E5" SIZE=4>' from dual;
select '<FONT FACE="VERDANA" COLOR="#000951" SIZE=4>' from dual;
select case
when am.NAME_STATUS='VeryCritical'
then '<tr bgcolor=red>'
when am.NAME_STATUS='NeedAction'
then '<tr bgcolor=yellow>'
when am.NAME_STATUS='UnderMonitoring'
then '<tr bgcolor=green>'
else '<tr bgcolor="#amber">' end ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.HOST_NAME ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.TARGET_NAME ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.TARGET_TYPE ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.TARGET_GUID  ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.TABLESPACE_NAME ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.TBS_SIZE  ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.TBS_USED_SIZE ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am."PCT_FREE" ||
'<td> <FONT FACE="VERDANA" SIZE=2.5>' || am.NAME_STATUS
from
(
select p.HOST_NAME as HOST_NAME ,p.TARGET_NAME as TARGET_NAME ,p.TARGET_TYPE as TARGET_TYPE,p.TARGET_GUID  as TARGET_GUID, p.TABLESPACE_NAME as TABLESPACE_NAME,p.TBS_SIZE as TBS_SIZE,p.TBS_USED_SIZE as TBS_USED_SIZE,p.PCT_FREE as PCT_FREE,case when p.PCT_FREE > 10 and p.PCT_FREE <= 20 then 'NeedAction' when p.PCT_FREE >= 20 and p.PCT_FREE < 50 then 'UnderMon' when p.PCT_FREE <= 10  then 'VeryCritical'
else 'NO_Action_Required' END as NAME_STATUS
from
(select
HOST_NAME,
TARGET_NAME,
TARGET_TYPE,
TARGET_GUID,
TABLESPACE_NAME,
TABLESPACE_SIZE/1024/1024/1024 TBS_SIZE,
TABLESPACE_USED_SIZE/1024/1024/1024 TBS_USED_SIZE,
round(nvl((TABLESPACE_SIZE-TABLESPACE_USED_SIZE),0)*100/TABLESPACE_SIZE) PCT_FREE
from mgmt$db_tablespaces
where round(nvl((TABLESPACE_SIZE-TABLESPACE_USED_SIZE),0)*100/TABLESPACE_SIZE) < 30 and TABLESPACE_SIZE >0
and  TARGET_NAME in (select TARGET_NAME from MGMT$DB_INIT_PARAMS_ALL where NAME = 'cluster_database' and VALUE='FALSE' )
order by 2,5,7) p
) am  order by am.PCT_FREE;

select '<FONT FACE="VERDANA" COLOR="#E5E5E5" SIZE=4>' from dual;
select '<FONT FACE="VERDANA" COLOR="#000951" SIZE=4>' from dual;
select '</table>' from dual;
select '</body >' from dual;