Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Display the top 5 salaries for each department using single SQL Oracle
  • compile_inv.sql Oracle
  • segment_wise_space.sql segspace.sql Segment wise space usage (allocated and used) Oracle
  • Zip and unzip with tar Linux/Unix
  • Roles and Stored Object behaviour Oracle
  • To check whether standby is recovering properly or not?? Oracle
  • Finding locked objects Oracle
  • rm_backup_arch_file.ksh Linux/Unix
  • OPENING A STANDBY DATABASE IN READ-ONLY MODE Oracle
  • sql_doing_fts.sql Oracle
  • logminer and my_lbu Oracle
  • Debugging Shell FIles Linux/Unix
  • telnet listening Linux/Unix
  • Proc Compilation Oracle
  • Running select from V$ views from remote server Linux/Unix

get_ratio.sql get the ratio of users from v$session and this uses CASE-WHEN-THEN clause

Posted on 10-May-2008 By Admin No Comments on get_ratio.sql get the ratio of users from v$session and this uses CASE-WHEN-THEN clause

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;

Oracle, SQL scripts

Post navigation

Previous Post: pvm_metric.sql for gathering report from vmstat tables
Next Post: temp_use.sql diplays usage of temp ts

Related Posts

  • Jai Shree Ram Oracle
  • 10g RAC: Troubleshooting CRS Root.sh Problems Oracle
  • How to calculate PROCESSES parameter Oracle
  • Good link for LIO in Oracle ( Logical IOs) Oracle
  • Rename Tablespace Oracle
  • create database syntax Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • catall.sh Linux/Unix
  • For Perl DBI installation and testing program PHP/MYSQL/Wordpress
  • Giving Grant on v$DATABASE Oracle
  • 339939.1 Running Cluster Verification Utility to Diagnose Install Problems Oracle
  • ext#.sql Oracle
  • Parallel DML Oracle
  • Order by with ROWNUM Oracle
  • db_status.sql Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme