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 off

HTML OUTPUT:

Enjoy!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s