set echo off
set verify off
set serveroutput on size 100000
set linesize 100
set pagesize 120
col machine format a20
col service_name format a20
select inst_id, machine, service_name, count(*) from gv$session where service_name='PROD.liamlamth.local' group by inst_id, machine, service_name;
select inst_id, machine, service_name, count(*) from gv$session where service_name='PROD.liamlamth.local' group by inst_id, machine, service_name;
select inst_id, machine, service_name, count(*) from gv$session where service_name='PROD.liamlamth.local' group by inst_id, machine, service_name;
set feedback off
declare
inst1_cnt number;
inst2_cnt number;
inst3_cnt number;
inst4_cnt number;
inst5_cnt number;
inst1_shcnt number;
inst2_shcnt number;
inst3_shcnt number;
inst4_shcnt number;
inst5_shcnt number;
diff_cnt number;
begin
select count(*) into inst1_cnt from gv$session where type='USER' and service_name='PROD.liamlamth.local' and inst_id=1;
select count(*) into inst2_cnt from gv$session where type='USER' and service_name='PROD.liamlamth.local' and inst_id=2;
select count(*) into inst3_cnt from gv$session where type='USER' and service_name='PROD.liamlamth.local' and inst_id=3;
select count(*) into inst4_cnt from gv$session where type='USER' and service_name='PROD.liamlamth.local' and inst_id=4;
select count(*) into inst5_cnt from gv$session where type='USER' and service_name='PROD.liamlamth.local' and inst_id=5;
--#if inst1_cnt >= inst2_cnt then
--#diff_cnt := inst1_cnt - inst2_cnt;
dbms_output.put_line('PROD.liamlamth.local_RAC1 service=PROD.liamlamth.local: '||inst1_cnt);
dbms_output.put_line('PROD.liamlamth.local_RAC2 service=PROD.liamlamth.local: '||inst2_cnt);
dbms_output.put_line('PROD.liamlamth.local_RAC3 service=PROD.liamlamth.local: '||inst3_cnt);
dbms_output.put_line('PROD.liamlamth.local_RAC4 service=PROD.liamlamth.local: '||inst4_cnt);
dbms_output.put_line('PROD.liamlamth.local_RAC5 service=PROD.liamlamth.local: '||inst5_cnt);
end;
/
exit