www.pudn.com > oramon.zip > tbsmon.sql

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; 
/