Prev: ORA-27369: job of type EXECUTABLE failed with exit code: Permission denied
Next: ORA-00904 over DBLINK
From: Michel Cadot on 23 Dec 2008 11:22

"Mark D Powell" <Mark.Powell(a)eds.com> a �crit dans le message de news:
28e57ec0-83da-447f-b2ef-4dd181e65522(a)v39g2000pro.googlegroups.com...
On Dec 23, 3:35 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <m...(a)pixar.com> a �crit dans le message de news: U914l.8677$W06.8...(a)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


From: Michel Cadot on 23 Dec 2008 11:22

"Mark D Powell" <Mark.Powell(a)eds.com> a �crit dans le message de news:
ba17939d-abab-42eb-9464-a0f7248cfa91(a)z27g2000prd.googlegroups.com...
On Dec 23, 3:43 am, Laurenz Albe <inv...(a)spam.to.invalid> wrote:
> m...(a)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


From: mh on 24 Dec 2008 03:42
Michel Cadot <micadot{at}altern{dot}org> wrote:
> "Mark D Powell" <Mark.Powell(a)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


--
Mark Harrison
Pixar Animation Studios
From: Mark D Powell on 24 Dec 2008 11:40
On Dec 24, 3:42 am, m...(a)pixar.com wrote:
> Michel Cadot <micadot{at}altern{dot}org> wrote:
> > "Mark D Powell" <Mark.Pow...(a)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
>
> --
> 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 --
First  |  Prev  | 
Pages: 1 2
Prev: ORA-27369: job of type EXECUTABLE failed with exit code: Permission denied
Next: ORA-00904 over DBLINK