tbsmon.sql

一个Oracle性能监视工具

set feedback off
set verify off
set serveroutput on
declare

t_number1 number :=0;
t_lines   number :=&1;

cursor c_tbs is
select lower(f.tablespace_name) tbsname
      ,max(d.Tot_grootte_Mb) tbssize
      ,max(d.Tot_grootte_Mb) - round(sum(f.bytes)/(1024*1024),2) tbsused
      ,(max(d.Tot_grootte_Mb) - round(sum(f.bytes)/(1024*1024),2)) /
       max(d.Tot_grootte_Mb) * 100 tbspused
      ,round(sum(f.bytes)/(1024*1024),2) tbsfree
      ,count(f.bytes) tbsparts 
      ,round(max(f.bytes)/(1024*1024),2) tbsbig
from sys.dba_free_space f
,    (select dd.tablespace_name, round(sum(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
      from   sys.dba_data_files dd
      group by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name
group by f.tablespace_name
order by 1;

begin
 dbms_output.enable(100000);

 -- detailed sessions
 dbms_output.put_line('TABLESPACE_NAME                   SIZE    USED  %USED    FREE   PARTS BIGGEST');
 t_number1 := 0;
 for r_tbs in c_tbs 
 loop
   dbms_output.put_line(rpad(ltrim(r_tbs.tbsname),30)                            ||
                        lpad(ltrim(to_char(r_tbs.tbssize,'9990.00')),8)          ||
                        lpad(ltrim(to_char(r_tbs.tbsused,'9990.00')),8)          ||
                        lpad(ltrim(to_char(r_tbs.tbspused,'990.00')),7)          ||
                        lpad(ltrim(to_char(r_tbs.tbsfree,'9990.00')),8)          ||
                        lpad(ltrim(to_char(r_tbs.tbsparts,'9999990')),8)          ||
                        lpad(ltrim(to_char(r_tbs.tbsbig,'9990.00')),8));
   t_number1 := t_number1 + 1;
   exit when t_number1 = t_lines;
 end loop;
end;
/