Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to check no of connection

 
Vijay Kumar
Ranch Hand
Posts: 260
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
on sqlplus run the query


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

Shailesh
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic