Home » RDBMS Server » Performance Tuning » please help for sql query to find out total number of concurrent connections in database (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
please help for sql query to find out total number of concurrent connections in database [message #670452] Thu, 05 July 2018 00:48 Go to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi,

Please can you help me to know simple sql query to find out total number of concurrent connections in database.
Re: please help for sql query to find out total number of concurrent connections in database [message #670453 is a reply to message #670452] Thu, 05 July 2018 00:50 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
Count v$session, where the username is null.

--correction (thank you, MC): ...is NOT null

[Updated on: Thu, 05 July 2018 02:48]

Report message to a moderator

Re: please help for sql query to find out total number of concurrent connections in database [message #670454 is a reply to message #670453] Thu, 05 July 2018 00:59 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Thanks John.

Re: please help for sql query to find out total number of concurrent connections in database [message #670455 is a reply to message #670454] Thu, 05 July 2018 01:18 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi John,

When creating function to call in select query then it's giving compiler error.


CREATE OR REPLACE FUNCTION TOT_CONCURR_SESSION 
RETURN NUMBER
AS

 v_tot_session number := 0;
begin

select  count(1)
into    v_tot_session
from    v$session
where   username is null;

return v_tot_session;

end;


Error: Error(8,1): PL/SQL: SQL Statement ignored
Error(10,9): PL/SQL: ORA-00942: table or view does not exist

[Updated on: Thu, 05 July 2018 01:18]

Report message to a moderator

Re: please help for sql query to find out total number of concurrent connections in database [message #670457 is a reply to message #670455] Thu, 05 July 2018 01:51 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
Create the function to run with invoker's rights.
Re: please help for sql query to find out total number of concurrent connections in database [message #670459 is a reply to message #670455] Thu, 05 July 2018 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is IS NOT NULL, username NULL are for background processes.
Use COUNT(*) not count(1).

Re: please help for sql query to find out total number of concurrent connections in database [message #670465 is a reply to message #670459] Thu, 05 July 2018 06:57 Go to previous message
msol25
Messages: 396
Registered: June 2011
Senior Member
Thanks Michel,

I have done correction at my end.

Have a nice day.
Previous Topic: Oracle Text index column when searching multiple tables
Next Topic: Sql query to get Max,min and max-1 row from a table
Goto Forum:
  


Current Time: Sun Dec 08 18:47:28 CST 2019