Skip to Main Content
  • Questions

  • DB Client Process/Session Identification from Unix

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: September 04, 2007 - 6:12 pm UTC

Last updated: May 06, 2013 - 4:55 pm UTC

Version: 9I

Viewed 10K+ times! This question is

You Asked

Hi Tom ,

1)How to identify Client connections(connections from say webserver or toad or sqlplus) to the Database from unix. Please see the ps output.
How do I know by just looking at this info that it is a client process ?
Is it got to do with (LOCAL=NO) entrees ?

I thought these were client processes and killed some of them them and that resulted in the
DB shutdown dont know why.


2)What does "(LOCAL=NO)" mean ?

3)How to map a unix process id to a DB session. ?


$ ps -ef|grep MYSID | sort +8
oracle 11138 1 0 17:20:58 ? 0:00 ora_pmon_MYSID
oracle 11140 1 0 17:20:58 ? 0:00 ora_dbw0_MYSID
oracle 11142 1 0 17:20:58 ? 0:00 ora_lgwr_MYSID
oracle 11144 1 0 17:20:58 ? 0:00 ora_ckpt_MYSID
oracle 11146 1 0 17:20:58 ? 0:00 ora_smon_MYSID
oracle 11148 1 0 17:20:58 ? 0:00 ora_reco_MYSID
oracle 11150 1 0 17:20:58 ? 0:00 ora_s000_MYSID
oracle 11152 1 0 17:20:58 ? 0:00 ora_d000_MYSID
oracle 11154 1 0 17:20:58 ? 0:00 ora_arc0_MYSID
oracle 11156 1 0 17:20:59 ? 0:00 ora_arc1_MYSID
oracle 11473 1 0 17:31:53 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11475 1 0 17:31:55 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11477 1 0 17:31:56 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11479 1 0 17:31:56 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11481 1 0 17:31:56 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11483 1 0 17:31:56 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11485 1 0 17:31:56 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11487 1 0 17:31:56 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11489 1 0 17:31:56 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11491 1 0 17:31:56 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11493 1 0 17:31:57 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11495 1 0 17:32:01 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11497 1 0 17:32:01 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11499 1 0 17:32:01 ? 0:00 oracleMYSID (LOCAL=NO)
oracle 11501 1 0 17:32:01 ? 0:01 oracleMYSID (LOCAL=NO)
oracle 11830 11583 1 17:48:13 pts/1 0:00 grep MYSID

and Tom said...

to do this from unix you would:

$ sqlplus u/p

and run a query.


You could look at the parent process id and see what the parent is (assuming dedicated server), the listener would be the parent of the remote connections and the actually progam (sqlplus, toad) would be the parent in a local connection.

but basically, the oracleMYSID processes are your dedicated servers.

[tkyte@dellpe ~]$ ps -auxww | grep $ORACLE_SID
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.3/FAQ
ora9ir2  17418  0.0  0.4 304704 8916 ?       Ss   Sep04   0:01 ora_pmon_ora9ir2
ora9ir2  17420  0.0  1.4 306000 30360 ?      Ss   Sep04   0:00 ora_dbw0_ora9ir2
ora9ir2  17422  0.0  0.4 309128 8580 ?       Ss   Sep04   0:03 ora_lgwr_ora9ir2
ora9ir2  17424  0.0  0.4 304956 9348 ?       Ss   Sep04   0:10 ora_ckpt_ora9ir2
ora9ir2  17426  0.0  1.2 304388 25640 ?      Ss   Sep04   0:03 ora_smon_ora9ir2
ora9ir2  17428  0.0  0.5 304144 10784 ?      Ss   Sep04   0:00 ora_reco_ora9ir2
ora9ir2  17430  0.0  0.4 304128 10080 ?      Ss   Sep04   0:00 ora_cjq0_ora9ir2
ora9ir2  17434  0.0  0.3 304588 7040 ?       Ss   Sep04   0:00 ora_s000_ora9ir2
ora9ir2  17436  0.0  0.3 304504 7172 ?       Ss   Sep04   0:00 ora_d000_ora9ir2
ora9ir2  17438  0.0  0.5 308224 11540 ?      Ss   Sep04   0:00 ora_arc0_ora9ir2
ora9ir2  17440  0.0  0.5 308224 11532 ?      Ss   Sep04   0:00 ora_arc1_ora9ir2
ora9ir2  17450  0.0  1.7 304724 37216 ?      Ss   Sep04   1:07 ora_qmn0_ora9ir2
tkyte    26016  0.0  0.0  4712  696 pts/1    S+   13:56   0:00 grep ora9ir2
[tkyte@dellpe ~]$ plus

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Sep 6 13:57:01 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

ops$tkyte%ORA9IR2> !ps -auxww | grep $ORACLE_SID
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.3/FAQ
ora9ir2  17418  0.0  0.4 304704 8916 ?       Ss   Sep04   0:01 ora_pmon_ora9ir2
ora9ir2  17420  0.0  1.4 306000 30360 ?      Ss   Sep04   0:00 ora_dbw0_ora9ir2
ora9ir2  17422  0.0  0.4 309128 8580 ?       Ss   Sep04   0:03 ora_lgwr_ora9ir2
ora9ir2  17424  0.0  0.4 304956 9348 ?       Ss   Sep04   0:10 ora_ckpt_ora9ir2
ora9ir2  17426  0.0  1.2 304388 25640 ?      Ss   Sep04   0:03 ora_smon_ora9ir2
ora9ir2  17428  0.0  0.5 304144 10784 ?      Ss   Sep04   0:00 ora_reco_ora9ir2
ora9ir2  17430  0.0  0.4 304128 10080 ?      Ss   Sep04   0:00 ora_cjq0_ora9ir2
ora9ir2  17434  0.0  0.3 304588 7040 ?       Ss   Sep04   0:00 ora_s000_ora9ir2
ora9ir2  17436  0.0  0.3 304504 7172 ?       Ss   Sep04   0:00 ora_d000_ora9ir2
ora9ir2  17438  0.0  0.5 308224 11540 ?      Ss   Sep04   0:00 ora_arc0_ora9ir2
ora9ir2  17440  0.0  0.5 308224 11532 ?      Ss   Sep04   0:00 ora_arc1_ora9ir2
ora9ir2  17450  0.0  1.7 304724 37216 ?      Ss   Sep04   1:07 ora_qmn0_ora9ir2
tkyte    26017  0.0  0.0  3980  940 pts/1    S+   13:57   0:00 rlwrap /home/ora9ir2/bin/sqlplus /
tkyte    26018  0.3  0.2 14076 5800 pts/2    Ss+  13:57   0:00 /home/ora9ir2/bin/sqlplus
<b>ora9ir2  26019  0.3  0.5 304152 11472 ?      Ss   13:57   0:00 oracleora9ir2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
</b>tkyte    26022  0.0  0.0  3652  692 pts/2    S+   13:57   0:00 grep ora9ir2


In my case, it is LOCAL=YES because I am local, not remote - you are not local, you are connecting over sqlnet - local = NO in this case - the client is remote.



3) v$process.

for example, using my example from above:

ops$tkyte%ORA9IR2> select a.spid dedicated_server,
  2             b.process clientpid
  3    from v$process a, v$session b
  4   where a.addr = b.paddr
  5     and b.sid = (select sid from v$mystat where rownum=1)
  6  /

DEDICATED_SE CLIENTPID
------------ ------------
26019        26018



26019 is my dedicated server, 26018 is the sqlplus client (that clientpid will be the REMOTE pid of the client in your case)

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Without V$ tables

Girish, April 26, 2013 - 5:02 am UTC

Hi Tom,

How to get process id if we don't have access to v$session and v$parameter .Is there any other way by which we can get ?

We need to use this in PL/SQL program

Thanks
Girish
Tom Kyte
April 26, 2013 - 1:51 pm UTC

why would you need to use process id in a plsql program if you cannot query up any useful information from it?

and you do know that process id can change from call to call - it won't be constant in a session necessarily right?

what are you doing with this process id, tell me the "goal" (not what you want to do) and perhaps we can suggest a good way to do it.

Girish, May 02, 2013 - 5:09 am UTC

Actually our instrumentation code(which logs into a table details) is called by multithreaded program and we would like to see tracefile generated by each thread and to identify which thread is mapped to which tracefile we need process id (part of trace file name).

This is done in a PL/SQL program
Tom Kyte
May 06, 2013 - 4:55 pm UTC

no you don't, you just need to use dbms_monitor to set your session identifier (any unique id you want) and then use trcsess to collection your trace records.

Or have each session set their tracefile identifier
http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams247.htm

to uniquely identify their tracefile.

DB Client Process/Server process

sudeshna, July 31, 2013 - 6:09 am UTC 

Hello Sir,
I have followed this example to get server process id & client process id.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 31 16:47:57 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> select a.spid server, b.process client, b.username
  2  from v$process a , v$session b
  3  where (a.addr = b.paddr);

SERVER       CLIENT       USERNAME
------------ ------------ ------------------------------
5265         5235         SYS
5267         5267
8948         8948
8677         8676         SYS
5277         5277
5292         5292
7143         5283         HR
7925         5283         HR

SQL> !ps -auxww | grep $ORACLE_SID
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.7/FAQ
oracle    5265  0.0  4.8 378732 49536 ?        Ss   16:01   0:01 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7143  0.0  2.9 376572 30120 ?        Ss   16:30   0:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7925  0.0  2.5 376572 25880 ?        Ss   16:33   0:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    8677  0.0  2.2 377588 23372 ?        Ss   16:47   0:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Here, it is showing information regarding server process id,
and here it is local connection. But, not showing about 
5283 information which is client process id.How to show
this information from ps command?

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.