Automating Oracle Oracle Tips by Mike Ault

 'dictionary cache',          
'library cache',             
 'sql area',
 'PL/SQL DIANA',
 'SEQ S.O.');
--
CURSOR get_var IS
 SELECT
value
 FROM
v$parameter
 SEE CODE DEPOT FOR FULL SCRIPTS
CURSOR get_time IS
 SELECT
sysdate
 FROM

dual;
--
  todays_date        DATE;
  mem_ratio        NUMBER;
  share_mem        NUMBER;
  variable_mem        NUMBER;
  cur        INTEGER;
  sql_com        VARCHAR2(60);
  row_proc        NUMBER;
--
BEGIN
 OPEN get_share;
 OPEN get_var;
 FETCH get_share INTO share_mem;
 DBMS_OUTPUT.PUT_LINE('share_mem: '||to_char(share_mem));
 FETCH get_var INTO variable_mem;
 DBMS_OUTPUT.PUT_LINE('variable_mem: '||to_char(variable_mem));
 mem_ratio:=share_mem/variable_mem;
 DBMS_OUTPUT.PUT_LINE(TO_CHAR(mem_ratio,'99.999')||' '||TO_CHA
(p_free/100,'99.999'));
 IF mem_ratio>p_free/100 THEN

---

For more information on this topic, I recommend Don Burleson's latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.