Thursday, February 7, 2013

Pl/SQL + Bash to compare v$session to max allowed

Assuming you have this encapsulated into a bash script that has set your environment correctly.  This will give you a percent used value, captured into the MAXPERCENT shell variable.

        MAXPERCENT=`sqlplus -s -L ${MONITOR_USER}/${CHECK_PASS}@${SID}  <<EOF
              set pages 9999
              set lines 132
              set trim on
              set trims on
              set echo off
              set recsep off
              set head off
              set feedback off
              set newpage none
              set serveroutput on
                DECLARE MaxAllowed number(4);
                CurrentSessions number(4);
                UsedPercent number(5,2);
                begin
                select value
                into MaxAllowed
                from v\\$parameter
                where name = 'sessions';
                select count(*)
                into CurrentSessions
                from v\\$session;
                UsedPercent := round((CurrentSessions / MaxAllowed)*100,0);
                dbms_output.Put_line(UsedPercent);
                end;
                /
        exit
EOF`

No comments:

Post a Comment