set time on
set timing on
set verify off
select to_char(sysdate, ‘DD-MON-RRRR:HH24:MI’) from dual;
prompt ‘Total distinct users and total user sessions’
select count(distinct username), count(1), round(count(1)/count(distinct username),1 )
from v$session
where username is not null;
prompt ‘Total terminal server distinct users and total user sessions’
select count(distinct username), count(1), round(count(1)/count(distinct username),1 )
from v$session
where username is not null and instr(machine, ‘TS’) != 0;
column osuser format a10
column username format a30
column machine format a30
column program format a40
column sid format 99999999
column serial# format 99999999
column dt1 format a20
compute sum of “TOTAL_SESSIONS” on report
compute sum of “TOTAL_UNIQ_USERS” on report
break on report
set lines 141
set pages 500
select (case
when instr(osuser, ‘live’) != 0 then ‘liveops’
when instr(osuser, ‘neova’) != 0 then ‘neova’
when instr(osuser, ‘alamo’) != 0 then ‘alamo’
when substr(osuser, 1,1)= ‘u’ then ‘uxxxx’
else ‘others’
end ) OS_USER ,
substr(machine, 1, 13) MACHINE,
count(1) “TOTAL_SESSIONS”, count(distinct username) “TOTAL_UNIQ_USERS” ,
round(count(1)/count(distinct username),1) “Ratio”
from v$session
where substr(machine, 1, 13) = ‘FLOWERS_NTTS’
group by (case
when instr(osuser, ‘live’) != 0 then ‘liveops’
when instr(osuser, ‘neova’) != 0 then ‘neova’
when instr(osuser, ‘alamo’) != 0 then ‘alamo’
when substr(osuser, 1,1)= ‘u’ then ‘uxxxx’
else ‘others’
end ), substr(machine, 1, 13)
order by 2;
select (case
when instr(osuser, ‘live’) != 0 then ‘liveops’
when instr(osuser, ‘neova’) != 0 then ‘neova’
when instr(osuser, ‘alamo’) != 0 then ‘alamo’
when substr(osuser, 1,1)= ‘u’ then ‘uxxxx’
else ‘others’
end ) OS_USER ,
substr(machine, 1, 13) MACHINE,
count(1) “TOTAL_SESSIONS”, count(distinct username) “TOTAL_UNIQ_USERS” ,
round(count(1)/count(distinct username),1) “Ratio”
from v$session
where substr(machine, 1, 13) != ‘FLOWERS_NTTS’
and substr(machine, 1, 10) = ‘FLOWERS_NT’
group by (case
when instr(osuser, ‘live’) != 0 then ‘liveops’
when instr(osuser, ‘neova’) != 0 then ‘neova’
when instr(osuser, ‘alamo’) != 0 then ‘alamo’
when substr(osuser, 1,1)= ‘u’ then ‘uxxxx’
else ‘others’
end ), substr(machine, 1, 13)
order by 2;
select (case
when instr(osuser, ‘live’) != 0 then ‘liveops’
when instr(osuser, ‘neova’) != 0 then ‘neova’
when instr(osuser, ‘alamo’) != 0 then ‘alamo’
when substr(osuser, 1,1)= ‘u’ then ‘uxxxx’
else ‘others’
end ) OS_USER ,
substr(machine, 1, 13) MACHINE,
count(1) “TOTAL_SESSIONS”, count(distinct username) “TOTAL_UNIQ_USERS” ,
round(count(1)/count(distinct username),1) “Ratio”
from v$session
where substr(machine, 1, 10) != ‘FLOWERS_NT’
group by (case
when instr(osuser, ‘live’) != 0 then ‘liveops’
when instr(osuser, ‘neova’) != 0 then ‘neova’
when instr(osuser, ‘alamo’) != 0 then ‘alamo’
when substr(osuser, 1,1)= ‘u’ then ‘uxxxx’
else ‘others’
end ), substr(machine, 1, 13)
order by 2;