Showing posts with label Find Oracle Database session from OS process. Show all posts
Showing posts with label Find Oracle Database session from OS process. Show all posts

Tuesday 6 December 2016

Find Oracle Database session from OS process

Execute the following query and find out the Database session

SQL> select a.spid server, b.process client, b.username,b.status
from v$process a , v$session b
where (a.addr = b.paddr)
and  b.username is not null
order by a.spid asc;  2    3    4    5

SERVER                   CLIENT                   USERNAME                       STATUS
------------------------ ------------------------ ------------------------------ --------
14674                    2868:6052                SYS                            INACTIVE
14679                    2868:4792                SYS                            INACTIVE
14754                    14753                    SYS                            ACTIVE

SQL>

Execute the following command and find out the OS process mapping with Database session

[oracle@saidrasel ~]$ ps -auxww | grep $ORACLE_SID | grep LOCAL

oracle   14674  0.0  1.0 1682420 48532 ?       Ss   11:13   0:00 oracleorcl (LOCAL=NO)
oracle   14679  0.0  0.4 1679844 21852 ?       Ss   11:13   0:00 oracleorcl (LOCAL=NO)
oracle   14754  0.0  0.4 1679844 23068 ?       Ss   11:20   0:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
[oracle@saidrasel ~]$

Now create three new connection in Database and execute the query

SQL> select a.spid server, b.process client, b.username,b.status
from v$process a , v$session b
where (a.addr = b.paddr)
and  b.username is not null
order by a.spid asc  2    3    4    5  ;

SERVER                   CLIENT                   USERNAME                       STATUS
------------------------ ------------------------ ------------------------------ --------
14674                    2868:6052                SYS                            INACTIVE
14679                    2868:4792                SYS                            INACTIVE
14754                    14753                    SYS                            ACTIVE
14785                    2868:4792                SAID                         INACTIVE
14787                    2868:4792                SAID                         INACTIVE
14790                    3944:3764                RASEL                  INACTIVE

6 rows selected.

SQL>

Now find the these Database session from OS process also

[oracle@saidrasel ~]$ ps -auxww | grep $ORACLE_SID | grep LOCAL
oracle   14674  0.0  1.0 1682420 48540 ?       Ss   11:13   0:00 oraclestlbas (LOCAL=NO)
oracle   14679  0.0  0.4 1679844 21852 ?       Ss   11:13   0:00 oraclestlbas (LOCAL=NO)
oracle   14754  0.0  0.4 1679844 23096 ?       Ss   11:20   0:00 oraclestlbas (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   14785  0.1  0.8 1682420 40408 ?       Ss   11:23   0:00 oraclestlbas (LOCAL=NO)
oracle   14787  0.1  0.8 1680868 42848 ?       Ss   11:23   0:00 oraclestlbas (LOCAL=NO)
oracle   14790  0.0  0.4 1679844 22528 ?       Ss   11:23   0:00 oraclestlbas (LOCAL=NO)
[oracle@saidrasel ~]$