How to get client process id and oracle shadow process id in dedicated server?

— note this will work when not logged in as SYS, as the session id is the same for all background processes
SELECT p.spid oracle_dedicated_process, s.process clientpid FROM v$process p, v$session s WHERE p.addr = s.paddr AND s.audsid = userenv(‘sessionid’);

Or for SYS you can use the following
SQL> SELECT p.spid oracle_dedicated_process, s.process clientpid FROM v$process p, v$session s WHERE p.addr = s.paddr AND s.sid = (select sid from v$mystat where rownum = 1);

ORACLE_DEDIC CLIENTPID
———— ————
2301958 839726

1 row selected.

SQL> !ps -ef | grep 2301958
oracle 2301958 839726 0 15:18:04 – 0:00 oracleTEST (DESCRIPTION=(LOCAL=YES(ADDRESS=(PROTOCOL=beq)))
oracle 2441306 839726 0 15:18:13 pts/4 0:00 grep 2301958

SQL> !ps -ef | grep 839726
oracle 839726 2928804 0 15:18:02 pts/4 0:00 sqlplus
oracle 2441308 839726 0 15:18:19 pts/4 0:00 grep 839726

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s