p205.sql

oracal的入门知识

set heading off
column what format a40
column value format a30

select 'db instance' what, user || '@' || global_name value from global_name
UNION
select '# rows in v$segstat', to_char(count(*)) from v$segstat;

set linesize 200
set time on
set serveroutput on size 300000

declare
  l_temp        char(1);
  l_before      number;
  l_after       number := 0;
  l_loop_times  pls_integer := 1000;    -- try 1000
  l_sleep       number   := 0.00;    -- makes no difference
 
  cursor c_seg is select * from v$segstat;
  r_seg  c_seg%ROWTYPE;

   function get_mem return number is
     cursor c_mem is select bytes from v$sgastat
        where name = 'free memory' and pool = 'shared pool';
     r_mem   c_mem%ROWTYPE;
     begin
       open c_mem; fetch c_mem into r_mem; close c_mem;
       return r_mem.bytes;
     end get_mem;
begin
  l_after := get_mem();  

  for x in 1..l_loop_times loop
    l_before := l_after;

    OPEN c_seg; FETCH c_seg INTO r_seg; CLOSE c_seg;

    l_after := get_mem();
    dbms_output.put_line ('Loop ' || x || ': (' ||
       to_char(sysdate,'hh24:mi:ss') || ') from ' ||
       to_char(l_before,'999,999,999') || ' to ' ||
       to_char(l_after,'999,999,999') || ' (loss of ' ||
       to_char((l_before-l_after),'9,999,999') || ')');
    dbms_lock.sleep(l_sleep);
  end loop;
end;
/