get the sid,serial# of my connection?

[m...@pixar.com]

How can I programmatically get the SID and SERIAL# for my connection
from a client side program?

Many tia!
Mark

--
Mark Harrison
Pixar Animation Studios

[Michel Cadot]

<m...@pixar.com> a écrit dans le message de news: U914l.8677$W06....@flpi148.ffdc.sbc.com...

SID can be gotten with SYS_CONTEXT('USERENV','SID')
As far as I know there is no way to get SERIAL# unless you have priviledge on V$SESSION.

Regards
Michel

[Laurenz Albe]

m...@pixar.com wrote:
> How can I programmatically get the SID and SERIAL# for my connection
> from a client side program?

With the following SQL query:

SELECT dbms_debug_jdwp.current_session_id sid,
dbms_debug_jdwp.current_session_serial serial#
FROM dual;

Yours,
Laurenz Albe

[Mark D Powell]

On Dec 23, 3:35 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <m...@pixar.com> a écrit dans le message de news: U914l.8677$W06.8...@flpi148.ffdc.sbc.com...

> | How can I programmatically get the SID and SERIAL# for my connection
> | from a client side program?
> |
> | Many tia!
> | Mark
> |
> | --
> | Mark Harrison
> | Pixar Animation Studios
>
> SID can be gotten with SYS_CONTEXT('USERENV','SID')
> As far as I know there is no way to get SERIAL# unless you have priviledge on V$SESSION.
>
> Regards
> Michel

I would just use:
UT1 > l
1 select username, sid, serial#
2 from v$session
3* where sid = (select sid from v$mystat where rownum = 1)
UT1 > /

USERNAME SID SERIAL#
------------------------------ ---------- ----------
MPOWEL01 57 32395

This should work back down to at least version 8.1.7.

HTH -- Mark D Powell --

[Mark D Powell]

Hey, I learned something new. thanks Laurenz.

[Michel Cadot]

"Mark D Powell" <Mark....@eds.com> a écrit dans le message de news:
28e57ec0-83da-447f...@v39g2000pro.googlegroups.com...

---------------------

Not everyone has access to v$session.
In this is the case, the following is most efficient.

select sid, serial#
from v$session
where sid=SYS_CONTEXT('USERENV','SID')

Regards
Michel

[Michel Cadot]

"Mark D Powell" <Mark....@eds.com> a écrit dans le message de news:

ba17939d-abab-42eb...@z27g2000prd.googlegroups.com...

-----------------------------------

So do I

Regards
Michel

[m...@pixar.com]

Michel Cadot <micadot{at}altern{dot}org> wrote:

> "Mark D Powell" <Mark....@eds.com> a ?crit dans le message de news:

>> Hey, I learned something new. thanks Laurenz.

> So do I

Thanks all... here's my results which are working great.

1. a shell script to kill a connection:

#!/bin/sh
# kill a specified oracle session

sid=$1; ser=$2; inst=$3
echo -n sys password:
stty -echo; read pass; stty echo
echo "alter system kill session '$sid,$ser';"|
sqlplus -SL sys/$pass@$inst as sysdba

2. a local function (this in python) to generate a call to this script.
I call this at the beginning of my test program and print the
string so I can cut and paste.

def killstring(curs):
"""return a string that will kill this db connection"""
curs.execute("""SELECT dbms_debug_jdwp.current_session_id,
dbms_debug_jdwp.current_session_serial,
sys_context('USERENV', 'INSTANCE_NAME')
FROM dual""")
(sid,serial,instance)=curs.fetchone()
s="oracle-killsession %s %s %s"%(sid,serial,instance)
return s

3. and a sample invocation

ohm ~/tst$ oracle-killsession 98 45809 tmpltest2
sys password:
System altered.

4. and from my client... hooray!!!

cx_Oracle.DatabaseError: ORA-00028: your session has been killed

Share and enjoy!

[Mark D Powell]

On Dec 24, 3:42 am, m...@pixar.com wrote:
> Michel Cadot <micadot{at}altern{dot}org> wrote:

> > "Mark D Powell" <Mark.Pow...@eds.com> a ?crit dans le message de news:

Why not have the routine issue the kill via execute immediate rather
than have to cut and paste?

HTH -- Mark D Powell --

[m...@pixar.com]

Mark D Powell <Mark....@eds.com> wrote:
> Why not have the routine issue the kill via execute immediate rather
> than have to cut and paste?

Ah, I should have explained in more detail what I'm trying to do...
I have an application server I've written, that basically is an
infinite loop:

initialize connections
loop forever:
receive XML-RPC request
send request to db
send XML-RPC response

I have some connection failure detection and recovery code
in the loop, and I want to exercise that code, so I want to
cause the connection to randomly fail outside of the control
of the app server. That's also why I'm looking for other ways
to break the connection, in order to test all the applicable
failure modes.

thanks!

[joel garry]

On Dec 29, 2:02 pm, m...@pixar.com wrote:

There is always the failure of 'alter session kill' to deal with. On
unix I habitually kill the process instead. Other people have other
ideas about that (something about it being bad, aside from the danger
of killing the wrong process, but I can't remember why offhand), but
of course I'm only going to advocate what I do. In the past, I've
seen alter session kill just leave things out there forever, since it
needed to be cleaned up by smon, whereas pmon would be right on top of
things. Whether this applies to 10g, I couldn't say, I'm too busy
trying to figure out yet another config fragility that broke emctl on
one instance but not another.

I suspect there may be an issue with TCP cleanup acknowledgement with
the alter session kill, but I really don't know. I know that happens
with some non-Oracle things where the parent that spawned the Oracle
session has gone away. Or something like that.

jg
--
@home.com is bogus.
And they supposedly finally fix the stupid 'ps: cmd is not a valid
field name' in... 10.2.0.6GC