How to list active / open connections in Oracle?

Question

Is there any hidden table, system variable or something to show active connections in a given moment?

score 207 · Accepted Answer · edited Feb 19 '16 at 08:37

207

Use the V$SESSION view.

V$SESSION displays session information for each current session.

edited Feb 19 '16 at 08:37

Yogesh lele

392
4
17

answered Jun 25 '09 at 10:21

pauljwilliams

19,079
3
51
79

7

Error starting at line 1 in command: select * from FROM v$session Error at Command Line:1 Column:14 Error report: SQL Error: ORA-00903: invalid table name 00903. 00000 - "invalid table name" *Cause: *Action: – pistacchio Jun 25 '09 at 10:22
3

Either you don't have permissions, or you didn't install the DBA views correctly. – S.Lott Jun 25 '09 at 10:24
4

You'll need the select_catalog_role role. – pauljwilliams Jun 25 '09 at 10:25
2

You can join v$sqltext to get the current SQL of sessions too. – Alkini Jun 25 '09 at 16:46
32

pistacchio, you have 2 "from FROM" in the SQL: "select * from FROM v$session" – marcprux Nov 23 '10 at 20:02
@Alkini, over which fields should I run the join? I couldn't find any similar fields on both tables – LuizAngioletti Apr 12 '17 at 13:32
Before running queries on `V_$SESSION`, you need to grant access for your user. Run the following query using your sysadmin user: `GRANT SELECT ON V_$SESSION TO username;` – Pravin Bansal Dec 19 '17 at 20:21

score 128 · Answer 2 · answered Feb 01 '12 at 09:50

For a more complete answer see: 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;

score 33 · Answer 3 · answered Jan 29 '13 at 11:33

33

When I'd like to view incoming connections from our application servers to the database I use the following command:

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

Simple, but effective.

answered Jan 29 '13 at 11:33

user2021477

331
3
2

score 8 · Answer 4 · edited Nov 21 '17 at 09:21

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

This query attempts to filter out all background processes.

score 7 · Answer 5 · edited Jun 15 '15 at 06:37

7

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

edited Jun 15 '15 at 06:37

josliber

43,891
12
98
133

answered Jun 15 '15 at 06:31

Juber

71
1
1

score 5 · Answer 6 · edited Jun 15 '15 at 06:37

5

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

edited Jun 15 '15 at 06:37

josliber

43,891
12
98
133

answered Jul 17 '14 at 10:50

user3848789

69
1
1

4

Welcome to SO! Please provide some intuition for your answers. – vefthym Jul 17 '14 at 10:56

score 4 · Answer 7 · edited Dec 08 '16 at 22:48

4

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

edited Dec 08 '16 at 22:48

Tunaki

132,869
46
340
423

answered Jun 16 '15 at 15:05

Fletch F Fletch

411
5
7

score 4 · Answer 8 · edited Jan 11 '23 at 12:25

The following gives you list of operating system users sorted by number of connections, which is useful when looking for excessive resource usage.

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

SQL> select osuser, count(*) as active_conn_count from v$session group by osuser;

OSUSER ACTIVE_CONN_COUNT

tepadm 20 oracle 39

score 2 · Answer 9 · edited May 24 '17 at 00:45

2

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;

edited May 24 '17 at 00:45

miracle173

1,852
16
33

answered Mar 07 '17 at 06:09

kirankumar M

59
4

2

While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – DimaSan Mar 07 '17 at 11:06

score 0 · Answer 10 · answered Jan 11 '23 at 12:23

---check active seeeion---

SQL> SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session WHERE Status='ACTIVE' AND UserName IS NOT NULL;

   SID    SERIAL# USERNAME                       STATUS   SCHEMANAME                     LOGON_TIM

   247      28849 SYS                            ACTIVE   SYS                            11-JAN-23
   251      27524 SAPSR3                         ACTIVE   SAPSR3                         11-JAN-23

How to list active / open connections in Oracle?

10 Answers10

Linked

Related