I have to check the no of database connction open by application.
my application is deployed on Tomcat and databsee is oracle.
For your information, details of the V$SESSION view can be found in the Oracle Database Reference which is available from:
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?
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?
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.
[ April 06, 2005: Message edited by: Avi Abrami ]
In Oracle 9iR2, this doesnt work with SYS user
SQL> grant select on v$session to scott
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.