www.pudn.com > oramon.zip > dbfmon.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(d.tablespace_name) tbsname 
      ,substr(d.file_name,instr(d.file_name,'/',-1,1)+1) dbfname 
      ,d.bytes/(1024*1024) dbfsize 
      ,d.maxbytes/(1024*1024) dbfmaxsize 
      ,round(sum(f.bytes)/(1024*1024),2) dbffree 
      ,count(f.bytes) dbfparts  
      ,round(max(f.bytes)/(1024*1024),2) dbfbig 
      ,d.bytes/(1024*1024) - round(sum(f.bytes)/(1024*1024),2) dbfused 
from sys.dba_free_space f 
    ,sys.dba_data_files d 
where d.file_id = f.file_id 
group by d.tablespace_name,d.file_name,d.bytes,d.maxbytes 
order by 1,2; 
 
begin 
 dbms_output.enable(100000); 
 
 -- detailed sessions 
 dbms_output.put_line('TABLESPACE:FILE_NAME                 SIZE  MAXSIZE    USED    FREE PARTS BIGGEST'); 
 t_number1 := 0; 
 for r_tbs in c_tbs  
 loop 
   dbms_output.put_line(rpad(ltrim(r_tbs.tbsname)||':'|| 
                        ltrim(r_tbs.dbfname),33)                                 || 
                        lpad(ltrim(to_char(r_tbs.dbfsize,'9990.00')),8)          || 
                        lpad(ltrim(to_char(r_tbs.dbfmaxsize,'99990.00')),9)      || 
                        lpad(ltrim(to_char(r_tbs.dbfused,'9990.00')),8)          || 
                        lpad(ltrim(to_char(r_tbs.dbffree,'9990.00')),8)          || 
                        lpad(ltrim(to_char(r_tbs.dbfparts,'99990')),6)           || 
                        lpad(ltrim(to_char(r_tbs.dbfbig,'9990.00')),8)); 
   t_number1 := t_number1 + 1; 
   exit when t_number1 = t_lines; 
 end loop; 
end; 
/