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 197 · Accepted Answer · edited Feb 19 '16 at 08:37

197

Use the V$SESSION view.

V$SESSION displays session information for each current session.

edited Feb 19 '16 at 08:37

Yogesh lele

362
4
16

answered Jun 25 '09 at 10:21

PaulJWilliams

18,549
3
49
78

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
31

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 122 · 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 32 · Answer 3 · answered Jan 29 '13 at 11:33

32

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

321
3
2

score 7 · Answer 4 · 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,000
12
95
132

answered Jun 15 '15 at 06:31

Juber

71
1
1

score 7 · Answer 5 · 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 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,000
12
95
132

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

125,519
44
317
399

answered Jun 16 '15 at 15:05

Fletch F Fletch

363
4
7

score 4 · Answer 8 · edited Oct 04 '17 at 20:57

4

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

edited Oct 04 '17 at 20:57

maxschlepzig

31,679
12
125
164

answered Jul 27 '16 at 10:04

jediz

4,139
5
34
38

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,752
13
32

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

How to list active / open connections in Oracle?

9 Answers9

Linked

Related