OraFAQ Forum: Server Administration » how to find the sid from v$session of my current session as soon as it make connection with DB

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes there should be no rows in v$session for closed sessions.

Regards
Michel

Report message to a moderator

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

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.

Report message to a moderator

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

ramoradba
Messages: 2457
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

Report message to a moderator

Previous Topic:	Split 30GB DBF File into 6 - 5GB Files
Next Topic:	sql plan issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 27 05:58:25 CST 2025