aspose file tools*
The moose likes JDBC and the fly likes how to check no of connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to check no of connection" Watch "how to check no of connection" New topic
Author

how to check no of connection

Vijay Kumar
Ranch Hand

Joined: Jul 24, 2003
Posts: 260
hi all!!!
I have to check the no of database connction open by application.
my application is deployed on Tomcat and databsee is oracle.

Vijay
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

on sqlplus run the query


this will provide you details of all connection made to oracle including your sql plus

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Vijay,
For your information, details of the V$SESSION view can be found in the Oracle Database Reference which is available from:

http://tahiti.oracle.com

Although I don't use "Tomcat", I would imagine that there is probably a monitoring (or management) application (or utility or tool) that would provide you with this information. Have you tried asking in "Tomcat" related forums?

Good Luck,
Avi.
Alec Lee
Ranch Hand

Joined: Jan 28, 2004
Posts: 569
Avi,

About the v$session (although this isnt related to JDBC anymore), is it available to SYS only? How can I grant its SELECT privilege to other people?
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Alec,
An excerpt from Oracle Database Reference (chapter on dynamic performance views):

The catalog.sql script contains definitions of the views and public synonyms for the dynamic performance views. You must run catalog.sql to create these views and synonyms. After installation, only username SYS or anyone with SYSDBA role has access to the dynamic performance tables.

I believe the "catalog.sql" script is usually run as part of the installation process -- so you shouldn't need to run it.

In order to grant access, use the "GRANT" (SQL) statement -- but either as the SYS user, or some other user that has the SYSDBA role. See the Oracle SQL Reference for more details, but it should be something like:

But please verify that with the documentation -- I am only going from memory.

Good Luck,
Avi.
[ April 06, 2005: Message edited by: Avi Abrami ]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Avi Abrami:

grant select on SYS.V_$SESSION to SCOTT



This works for me

grant select on V$SESSION to SCOTT
[ April 06, 2005: Message edited by: Shailesh Chandra ]
Alec Lee
Ranch Hand

Joined: Jan 28, 2004
Posts: 569
Shailesh,

In Oracle 9iR2, this doesnt work with SYS user


SQL> grant select on v$session to scott
2 ;
grant select on v$session to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


Instead this is required:

grant select on SYS.V_$SESSION to SCOTT

I dont understand why. As I recall, granting on synonym (v$session) should be converted to base table (v_$session) grant.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Alec Y.L. Lee:

In Oracle 9iR2, this doesnt work with SYS user

Instead this is required:

grant select on SYS.V_$SESSION to SCOTT



I had tested it on Toad/Oracle 9i before posting, But i will try it on SQL plus
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Alec Y.L. Lee:

In Oracle 9iR2, this doesnt work with SYS user



I tried this on SQLPLUS and this works with a user having DBA rights.

Then I tried with sys user then I got the same error message which you have specified
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: how to check no of connection