Utilização de xml no banco

[vieriParticipante]

essa aqui fica com uma saida legal… 😆

CREATE OR REPLACE PROCEDURE “SYSTEM”.”PR_MONIT_TBPS”
is
l_body long := chr(10) ||’ ‘|| lpad(‘tsname’,10, ‘ ‘) || ‘ ‘
|| rpad(‘tbs_size_mb’, 12, ‘ ‘) || ”
|| rpad(‘used’, 20, ‘ ‘) || ‘ ‘
|| rpad(‘avail’, 20, ‘ ‘) || ‘ ‘
|| rpad(‘used_visual’, 20, ‘ ‘) || ‘ ‘
|| rpad(‘pct_used’, 8, ‘ ‘) || chr(10);
begin
for x in ( SELECT df.tablespace_name tsname
, round(sum(df.bytes)/1024/1024) tbs_size_mb
, round(nvl(sum(e.used_bytes)/1024/1024,0)) used
, round(nvl(sum(f.free_bytes)/1024/1024,0)) avail
, rpad(‘ ‘||rpad(‘X’,round(sum(e.used_bytes)
10/sum(df.bytes),0), ‘X’),11,’-‘) used_visual
, round(nvl((sum(e.used_bytes)100)/sum(df.bytes),0),2) pct_used
FROM sys.dba_data_files df
, (SELECT file_id
, sum(nvl(bytes,0)) used_bytes
FROM sys.dba_extents
GROUP BY file_id) e
, (SELECT max(bytes) free_bytes
, file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id(+) = df.file_id
AND df.file_id = f.file_id(+)
GROUP BY df.tablespace_name
ORDER BY 6 )
loop
l_body := l_body || lpad(x.tsname, 18, ‘ ‘) || ‘ ‘ ||
rpad(x.tbs_size_mb, 12, ‘ ‘) ||’ ‘|| rpad(x.used,20,’ ‘) ||’ ‘|| rpad(x.avail,2
0,’ ‘)
||’ ‘|| rpad(x.used_visual,20,’ ‘) ||’ ‘|| rpad(x.pct_used,5,’ ‘) || chr(10) ;
end loop;
mail.send
( p_sender_email => ”,
p_from => ‘Dba_monitor ‘,
p_to => mail.array( ”),
p_subject => ‘Consumo de Tablespace no RAC !’,
p_body => l_body );
end;