SQL QUERY TO GET TABLESPACE USAGE IN HTML FORMAT ORACLE
Everyone like colourful output which looks beautiful.If you ask tablespace usage report in html format to me and i have one for you. Note: You might get “ORA-01476: divisor is equal to zero” error due to size conversion from bytes to GB. If you get the above error, then you can convert bytes to MB using 1048576 in place of 1073741824
SQL Query:
col TOTAL_SPACE format 999999
col TABLESPACE_NAME format a20
col TOTAL_FREE_SPACE format 999999
col UTIL_PCT format 999999
select x.TABLESPACE_NAME,round((x.bytes/1073741824),2) as TOTAL_SPACE_GB,
round(x.bytes/1073741824,2) - round(sum(y.bytes/1073741824),2) as TOTAL_FREE_SPACE_GB,
to_number(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) as UTIL_PCT
from dba_data_files x
inner join dba_free_space y on x.TABLESPACE_NAME = y.TABLESPACE_NAME
group by x.TABLESPACE_NAME,x.bytes/1073741824;Normal output:
kish@exdbx<>kish@exdbx<>col TOTAL_SPACE format 999999
col TABLESPACE_NAME format a20
col TOTAL_FREE_SPACE format 999999
col UTIL_PCT format 999999
select x.TABLESPACE_NAME,round((x.bytes/1073741824),2) as TOTAL_SPACE_GB,
round(x.bytes/1073741824,2) - round(sum(y.bytes/1073741824),2) as TOTAL_FREE_SPACE_GB,
to_number(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) as UTIL_PCT
from dba_data_files x
inner join dba_free_space y on x.TABLESPACE_NAME = y.TABLESPACE_NAME
group by x.TABLESPACE_NAME,x.bytes/1073741824;kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<> 2 3 4 5 6
TABLESPACE_NAME TOTAL_SPACE_GB TOTAL_FREE_SPACE_GB UTIL_PCT
-------------------- ---------------------------------------- ---------------------------------------- --------
SYSTEM .97 .93 4
EXAMPLE .34 .3 12
UNDOTBS2 .05 .01 80
USERS 3.02 2.82 7
SYSAUX .81 .76 6
UNDOTBS1 .47 .05 89
6 rows selected.
SQL Query for HTML:
This HTML script display the threshold for tablespace size above threshold in red colour and tablespace size below threshold in green colour
SET MARKUP HTML ON SPOOL ON ENTMAP OFF -
head '<title> DAILY HEALTH CHECK </title> -
<style type="text/css"> -
table {background: #FFFFE0; font-size: 99%;} -
th { background-color: DarkBlue; color:White} -
td { padding: 0px; } -
</style>' -
body 'text=black bgcolor=FAFAD2 align=left' -
table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
spool /tmp/tb_check.html
col TOTAL_SPACE format 999999
col TABLESPACE_NAME format a20
col TOTAL_FREE_SPACE format 999999
col UTIL_PCT format 999999
select x.TABLESPACE_NAME,round((x.bytes/1073741824),2) as TOTAL_SPACE_GB,
round(x.bytes/1073741824,2) - round(sum(y.bytes/1073741824),2) as TOTAL_FREE_SPACE_GB,
case when to_number(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) > 50 then '<font color=red>' || to_char(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) || '</font>'
when to_number(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) < 50 then '<font color=green>' || to_char(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2)) || '</font>'
else to_char(round((round(sum(y.bytes/1073741824),2)/round(x.bytes/1073741824,2))*100,2))
end UTIL_PCT
from dba_data_files x
inner join dba_free_space y on x.TABLESPACE_NAME = y.TABLESPACE_NAME
group by x.TABLESPACE_NAME,x.bytes/1073741824;
spool off
set markup html offHTML OUTPUT:
Enjoy!