Home » RDBMS Server » Server Administration » how to find the sid from v$session of my current session as soon as it make connection with DB (11.1.0.7.0 and 11.2.0.1.0)
how to find the sid from v$session of my current session as soon as it make connection with DB [message #537364] Tue, 27 December 2011 23:49 Go to next message
kiran111
Messages: 49
Registered: October 2011
Member
Hi Experts,

Need your advice in how to find the sid from v$session of my current session.

I want to ask:
Let say I have open 10 TOAD session and only in one toad session i have fired a sql query ( the rest 9 session are just blank), then the rest 9 status are in inactive status.
Now, how i can figure out which sid ( from v$session ) belongs to which session as all the 9 session having nothing to run.
Quote:
My main intention is to find out the current session sid as soon as it make connection with DB.


Thanks
Re: how to find the sid from v$session of my current session as soon as it make connection with DB [message #537374 is a reply to message #537364] Wed, 28 December 2011 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how to find the sid from v$session of my current session


SQL> select sys_context('USERENV','SID') from dual;
SYS_CONTEXT('USERENV','SID')
---------------------------------------------------------
158


Regards
Michel
Re: how to find the sid from v$session of my current session as soon as it make connection with DB [message #537383 is a reply to message #537374] Wed, 28 December 2011 01:19 Go to previous messageGo to next message
kiran111
Messages: 49
Registered: October 2011
Member
Hi Michel,

Many thanks for the answer,
I don't know how i missed this query, once again thanks for your expert help.

-------
I have a small doubt,
let say total 10 DB connection are made in which
2 are from TOAD and 8 are from JAVA side,
only 1 is active that is from TOAD and all others are INACTIVE.
When I close the TOAD (which has INACTIVE session), that row is removed from v$session.
But still JAVA connection are lying in the v$session ( it is more than 2 days). I checked with JAVA team and according to them they closed those DB session and no more required, but still I can see those connection.
My doubt is then why these JAVA connections are still showing in
v$session, even it was closed long back. Any alternative to remove these from v$session ( the issue is that now we are unable to get more new connection and we cannot increase the connection limit.)

thanks
Re: how to find the sid from v$session of my current session as soon as it make connection with DB [message #537385 is a reply to message #537383] Wed, 28 December 2011 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
according to them they closed those DB session and no more required, but still I can see those connection.


Do not trust them, this is wrong.
If you ask Oracle to close the session, Oracle closes it regardless if it is a Java or other kind of program.

Regards
Michel

Re: how to find the sid from v$session of my current session as soon as it make connection with DB [message #537405 is a reply to message #537385] Wed, 28 December 2011 02:46 Go to previous messageGo to next message
kiran111
Messages: 49
Registered: October 2011
Member
Thanks Michel,

Quote:
If you ask Oracle to close the session, Oracle closes it regardless if it is a Java or other kind of program.


Does this means that there will be now rows for the closed connection in v$session.


Re: how to find the sid from v$session of my current session as soon as it make connection with DB [message #537408 is a reply to message #537405] Wed, 28 December 2011 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes there should be no rows in v$session for closed sessions.

Regards
Michel
Re: how to find the sid from v$session of my current session as soon as it make connection with DB [message #537410 is a reply to message #537408] Wed, 28 December 2011 03:13 Go to previous messageGo to next message
kiran111
Messages: 49
Registered: October 2011
Member
Hi Michel,

thanks a lot for the clarification, now i am going back to fight with JAVA team Smile.

once again thanks for your time.
Re: how to find the sid from v$session of my current session as soon as it make connection with DB [message #537411 is a reply to message #537410] Wed, 28 December 2011 03:31 Go to previous message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Check with their connection pooling and the server they are using....
for Ex:In My case, Initially tomcat will open 4 sessions which will be in inactive mode.
If you are opening/running/calling report using Java then Report builder will open two more sessions.

Thank you
Sriram
Previous Topic: Split 30GB DBF File into 6 - 5GB Files
Next Topic: sql plan issue
Goto Forum:
  


Current Time: Thu Mar 28 08:17:37 CDT 2024