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

set feedback off 
set verify off 
set serveroutput on 
declare 
 
t_number1 number :=0; 
t_number2 number :=0; 
t_number3 number :=0; 
t_number4 number :=0; 
t_number5 number :=0; 
t_number6 number :=0; 
t_number7 number :=0; 
t_number8 number :=0; 
t_number9 number :=0; 
 
t_varchar1 varchar2(200); 
t_varchar2 varchar2(200); 
 
cursor c_sga is 
select   replace(replace(name,'Size'),'Database','DB') name 
        ,round(value/1024/1024,2) value 
from     sys.v_$sga; 
 
begin 
 dbms_output.enable(100000); 
 
 -- total sessions 
 select name     into t_varchar1 from v$database; 
 select count(*) into t_number2 from v$session; 
 select count(*) into t_number3 from v$session where type = 'BACKGROUND'; 
 select count(*) into t_number4 from v$session where type = 'USER'; 
 select count(*) into t_number5 from v$session where type = 'USER' and status = 'ACTIVE'; 
 select count(*) into t_number6 from v$session where type = 'USER' and status = 'INACTIVE'; 
 dbms_output.put_line(lower(user)||'@'||t_varchar1|| '=>'|| 
                      t_number2||' processes: '|| 
                      t_number3||' background,'|| 
                      t_number4||' user('|| 
                      t_number5||' active,'|| 
                      t_number6||' inactive)'); 
 
 -- SGA details 
 select round(sum(value)/1024/1024,2) into t_number1 from v$sga; 
 t_varchar1 := 'SGA:'||t_number1||'Mb ('; 
 for r_sga in c_sga 
 loop 
   t_varchar1 := t_varchar1||','||rtrim(lower(replace(r_sga.name,' ')))||':'|| 
                             ltrim(to_char(r_sga.value,'9990.00')); 
 end loop; 
 t_varchar1 := replace(t_varchar1,'(,','(')||')'; 
 dbms_output.put_line(t_varchar1); 
 
 -- Disk space 
 select round(sum(bytes/1024/1024),2) into t_number1 from dba_data_files; 
 select round(sum(bytes/1024/1024),2) into t_number2 from dba_free_space; 
 t_number3:=t_number1 - t_number2; 
 t_number4:=round(t_number3/t_number1 ,4) * 100; 
 dbms_output.put_line('Disk Total:'      ||t_number1||'Mb'|| 
                      ' (free:'        ||t_number2||'Mb'|| 
                      ' used:'         ||t_number3||'Mb'|| 
                      ' percent used:' ||t_number4||'%)'); 
 
 -- Hitratios 
 select round((sum(decode(name, 'consistent gets',value, 0)) +  
            sum(decode(name, 'db block gets',value, 0)) -  
            sum(decode(name, 'physical reads',value, 0))) /  
         (sum(decode(name, 'consistent gets',value, 0)) +  
         sum(decode(name, 'db block gets',value, 0))) * 100,2) getal 
   into t_number1 
   from v$sysstat; 
 
 select round((sum(PINHITS) / sum(PINS)) * 100,2) 
   into t_number2 
   from v$librarycache; 
 
 select round((sum(PINS) / (sum(PINS) + sum(RELOADS))) * 100,2)       
   into t_number3 
   from v$librarycache; 
 
 select round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2) 
   into t_number4 
   from v$rowcache; 
 dbms_output.put_line('Hits Buffer:'           ||t_number1||'%'|| 
                      ' Library:'         ||t_number2||'%'|| 
                      ' Library misses:'  ||t_number3||'%'|| 
                      ' Data dictionary:' ||t_number4||'%'); 
 
 dbms_output.put_line(chr(27));  
end; 
/