dbfmon.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(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;
/