Comment lister les connexions actives / ouvertes dans Oracle?

Pour une réponse plus complète voir: http://dbaforums.org/oracle/index.php?showtopic=16834

select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
--       b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid; 

Lorsque je souhaite afficher les connexions entrantes de nos serveurs d'applications vers la base de données, j'utilise la commande suivante:

SELECT username FROM v$session 
WHERE username IS NOT NULL 
ORDER BY username ASC;

Simple, mais efficace.

select
  username,
  osuser,
  terminal,
  utl_inaddr.get_host_address(terminal) IP_ADDRESS
from
  v$session
where
  username is not null
order by
  username,
  osuser;

Ce qui suit vous donne la liste des utilisateurs du système d'exploitation triés par nombre de connexions, ce qui est utile lorsque vous recherchez une utilisation excessive des ressources.

select osuser, count(*) as active_conn_count 
from v$session 
group by osuser 
order by active_conn_count desc

Select count(1) From V$session
where status='ACTIVE'
/

select status, count(1) as connectionCount from V$SESSION group by status;

select s.sid as "Sid", s.serial# as "Serial#", nvl(s.username, ' ') as "Username", s.machine as "Machine", s.schemaname as "Schema name", s.logon_time as "Login time", s.program as "Program", s.osuser as "Os user", s.status as "Status", nvl(s.process, ' ') as "OS Process id"
from v$session s
where nvl(s.username, 'a') not like 'a' and status like 'ACTIVE'
order by 1,2

Cette requête tente de filtrer tous les processus d'arrière-plan.

select 
    count(1) "NO. Of DB Users", 
    to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') sys_time
from 
    v$session 
where 
    username is NOT  NULL;