Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Oracle10g – Using SQLAccess Advisor (DBMS_ADVISOR) with the Automatic Workload Repository Oracle
  • USE_NL and INDEX hints example Oracle
  • find_log_switch.sql Find log switches in graphical manner Oracle
  • Find Time Consuming SQL Statements in Oracle 10g Oracle
  • move_arch_files.ksh Linux/Unix
  • send email from unix mailx with attachment. Linux/Unix
  • initUOCIOTTO.ora Oracle
  • ORA-00064: object is too large to allocate on this O/S during startup Oracle
  • purge_trc.sh Linux/Unix
  • To see how much time or progress of long transaction Oracle
  • Create type and Grant on it. Oracle
  • Exadata Basics Oracle
  • online_ts_bkup.sql Oracle
  • Removing first line Linux/Unix
  • Good Oracle Architecture In Short and point to point Oracle

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

  • segment_wise_space.sql segspace.sql Segment wise space usage (allocated and used) Oracle
  • All Hints for Oracle Databases Oracle
  • Goldengate Tutorial Oracle
  • ORA-01220 Oracle
  • How to Use DBMS_STATS to Move Statistics to a Different Database Oracle
  • Proc Compilation 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 (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • 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

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • findx.sql /* Find Indexes on specified USER.TABLE_NAME */ Oracle
  • switchlogfile.sh Linux/Unix
  • Good RAC & Standby Notes Oracle
  • Running some SQL on multiple databases connecting using monitoring userid and password Linux/Unix
  • SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql Oracle
  • JSON/XML Types in Oracle Oracle
  • Temporary Tablespsace Temp tablespace behaviour Oracle
  • Resolving RMAN Hung Jobs Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme