7

Please note that even though it looks very similar, it's not a duplicate question with this link: How to list active / open connections in Oracle?

I'm not asking about the number of sessions, but connections. I'm aware that I can query the v$session view, but I don't know how many connections are being used there. If there is a way to derive from it, please enlighten me.

EDIT: I'm asking about the physical database connection to the database.

Community
  • 1
  • 1
Iwan Satria
  • 1,903
  • 1
  • 19
  • 22
  • Do you mean max allowed connection? – Rahul Nov 04 '14 at 11:02
  • 1
    https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5671284058977 – Multisync Nov 04 '14 at 11:41
  • What exactly do you think is the difference between sessions and connections? –  Nov 04 '14 at 12:26
  • No, I don't mean max number of allowed connection. I mean the number of currently established connections. Please correct me if my understanding is wrong, but my understanding so far is that one connection does not necessarily represent one session. It can be either 0 or even more than one. That's why I can't rely only on the number of records in the v$session view. – Iwan Satria Nov 04 '14 at 14:41

1 Answers1

5

Bit confused with your statement I'm not asking about the number of sessions, but connections.

Conceptually both are same. Every active session will correspond to a underlying active connection to the database.

Now, if you meant to know the max allowed connection limit then Documentation says

Maximum number of connections (system and application) across all databases in an instance = 2048

To know the allowed session configured to your database, you can query v$parameter view like

SELECT name, value 
  FROM v$parameter
 WHERE name = 'sessions'

If you want to know the Active session at any instance Out of total configured to allow then you can query v$session view using the Status column like

SELECT COUNT(*)
  FROM v$session
WHERE STATUS = 'ACTIVE'

You may want to refer this post How to check the maximum number of allowed connections to an Oracle database?

Community
  • 1
  • 1
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • 2
    Please correct me if I'm wrong. My understanding is that one connection may contain multiple sessions.That's why I'm assuming here that the number of records in the v$session view is not the accurate number depicting the number of connections. – Iwan Satria Nov 04 '14 at 14:36
  • Generally it's 1:1 mapping. For more information check this article https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5671284058977 – Rahul Nov 04 '14 at 14:42
  • 1
    I've just read that article from that link. I saw that someone asked the same question I had here and there has been no clear answer to that as well. Anyway, after reading *most* of the great discussions there, I realise that this question is not very relevant to the problem I'm facing. I'm still leaving this question open for now. Thanks for your respond. – Iwan Satria Nov 04 '14 at 17:13
  • 1
    If you are looking for `physical database connection` then you can check the running process in your system. Are you running on Linux/windows system? – Rahul Nov 04 '14 at 17:27
  • I'm running on Linux. Is it accurate for both dedicated and non-dedicated? – Iwan Satria Nov 04 '14 at 17:36
  • Running the command `ps -auxww | grep oracleora920` will should let you know the opened DB connection(s). – Rahul Nov 04 '14 at 17:40