get the sid,serial# of my connection?

mh RemoveThis @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
 >> Stay informed about: get the sid,serial# of my connection? 

On Dec 23, 3:35 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> a écrit dans le message de 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 --
 >> Stay informed about: get the sid,serial# of my connection? 

On Dec 23, 3:43 am, Laurenz Albe wrote:
> m....RemoveThis@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

Hey, I learned something new. thanks Laurenz.

-- Mark D Powell --
 >> Stay informed about: get the sid,serial# of my connection? 

"Mark D Powell" a écrit dans le message de
28e57ec0-83da-447f-b2ef-4dd181e65522 DeleteThis @v39g2000pro.googlegroups.com...
On Dec 23, 3:35 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> a écrit dans le message de 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 --

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

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
 >> Stay informed about: get the sid,serial# of my connection? 

"Mark D Powell" a écrit dans le message de
ba17939d-abab-42eb-9464-a0f7248cfa91 RemoveThis @z27g2000prd.googlegroups.com...
On Dec 23, 3:43 am, Laurenz Albe wrote:
> m... RemoveThis @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

Hey, I learned something new. thanks Laurenz.

-- Mark D Powell --

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

So do I

Regards
Michel
 >> Stay informed about: get the sid,serial# of my connection? 

Michel Cadot <micadot{at}altern{dot}org> wrote:
> "Mark D Powell" a ?crit dans le message de
>> 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


--
Mark Harrison
Pixar Animation Studios
 >> Stay informed about: get the sid,serial# of my connection? 

On Dec 24, 3:42 am, m....DeleteThis@pixar.com wrote:
> Michel Cadot <micadot{at}altern{dot}org> wrote:
> > "Mark D Powell" a ?crit dans le message de
> >> 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
>
> --
> Mark Harrison
> Pixar Animation Studios

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

HTH -- Mark D Powell --
 >> Stay informed about: get the sid,serial# of my connection? 

On Dec 29, 2:02 pm, m....RemoveThis@pixar.com wrote:
> Mark D Powell 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!
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios

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
 >> Stay informed about: get the sid,serial# of my connection? 

Mark D Powell 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!
Mark

--
Mark Harrison
Pixar Animation Studios
 >> Stay informed about: get the sid,serial# of my connection?