Mapping Oracle Database Sessions to Operating System Processes

Select Oracle user info including os pid

SQL Code

 1col "SID/SERIAL" format a10
 2col username format a20
 3col osuser format a15
 4col program format a50
 5select	s.sid || ',' || s.serial# "SID/SERIAL"
 6,	s.username
 7,	s.osuser
 8,	p.spid "OS PID"
 9,	s.program
10from	v$session s
11,	v$process p
12Where	s.paddr = p.addr
13order 	by to_number(p.spid)
14/

Sample Oracle Output:

 1SID/SERIAL USERNAME             OSUSER          OS PID                   PROGRAM
 2---------- -------------------- --------------- ------------------------ --------------------------------------------------
 31,51318                         oracle          1751                     oracle@localhost.localdomain (PMON)
 4238,32433                       oracle          1756                     oracle@localhost.localdomain (CLMN)
 52,46817                         oracle          1762                     oracle@localhost.localdomain (PSP0)
 6239,38809                       oracle          1768                     oracle@localhost.localdomain (VKTM)
 73,60083                         oracle          1775                     oracle@localhost.localdomain (GEN0)
 8240,38968                       oracle          1780                     oracle@localhost.localdomain (MMAN)
 9242,5837                        oracle          1789                     oracle@localhost.localdomain (GEN2)
105,43292                         oracle          1791                     oracle@localhost.localdomain (VOSD)
11241,6809                        oracle          1793                     oracle@localhost.localdomain (DIAG)
1215,1554    SYS                  oracle          1798                     oracle@localhost.localdomain (OFSD)
13249,54000                       oracle          1798                     oracle@localhost.localdomain (SCMN)
146,34531                         oracle          1802                     oracle@localhost.localdomain (DBRM)
15243,12974                       oracle          1806                     oracle@localhost.localdomain (VKRM)
167,13091                         oracle          1812                     oracle@localhost.localdomain (SVCB)
17244,5064                        oracle          1816                     oracle@localhost.localdomain (PMAN)
188,25470                         oracle          1818                     oracle@localhost.localdomain (DIA0)
19245,44799                       oracle          1822                     oracle@localhost.localdomain (DIA1)
209,52018                         oracle          1824                     oracle@localhost.localdomain (LMHB)
21247,23362                       oracle          1830                     oracle@localhost.localdomain (DBW0)
2210,26928                        oracle          1833                     oracle@localhost.localdomain (LGWR)
23246,14445                       oracle          1835                     oracle@localhost.localdomain (CKPT)
2412,54188                        oracle          1839                     oracle@localhost.localdomain (SMON)
25248,5389                        oracle          1844                     oracle@localhost.localdomain (SMCO)
2611,14458                        oracle          1850                     oracle@localhost.localdomain (RECO)
27250,4512                        oracle          1859                     oracle@localhost.localdomain (LREG)
2813,26195                        oracle          1865                     oracle@localhost.localdomain (PXMN)
2914,57678                        oracle          1877                     oracle@localhost.localdomain (MMON)
30252,34776                       oracle          1880                     oracle@localhost.localdomain (MMNL)
31254,26559                       oracle          1899                     oracle@localhost.localdomain (BG00)
3218,30813                        oracle          1899                     oracle@localhost.localdomain (BG00)
33251,1313                        oracle          1899                     oracle@localhost.localdomain (SCMN)
34253,36102                       oracle          1899                     oracle@localhost.localdomain (BG00)
3519,6764                         oracle          1899                     oracle@localhost.localdomain (BG00)
3624,36119                        oracle          1899                     oracle@localhost.localdomain (BG00)
37255,24331                       oracle          1913                     oracle@localhost.localdomain (BG01)
3820,48416                        oracle          1913                     oracle@localhost.localdomain (SCMN)
39257,12867                       oracle          1913                     oracle@localhost.localdomain (BG01)
4021,35536                        oracle          1913                     oracle@localhost.localdomain (BG01)
41256,34711                       oracle          1913                     oracle@localhost.localdomain (BG01)
42259,47947                       oracle          1924                     oracle@localhost.localdomain (BG02)
4323,697                          oracle          1924                     oracle@localhost.localdomain (BG02)
44258,29095                       oracle          1924                     oracle@localhost.localdomain (SCMN)
4522,11344                        oracle          1924                     oracle@localhost.localdomain (BG02)
46260,46618                       oracle          1936                     oracle@localhost.localdomain (GCW0)
47261,9692                        oracle          1950                     oracle@localhost.localdomain (TMON)
4826,24917                        oracle          1953                     oracle@localhost.localdomain (RCBG)
494,45620                         oracle          2116                     oracle@localhost.localdomain (TT00)
50263,14430                       oracle          2119                     oracle@localhost.localdomain (TT01)
5132,28875                        oracle          2826                     oracle@localhost.localdomain (AQPC)
52265,47138                       oracle          3157                     oracle@localhost.localdomain (QM02)
5335,53257                        oracle          3170                     oracle@localhost.localdomain (Q003)
5446,26591                        oracle          3809                     oracle@localhost.localdomain (CL00)
5547,56309                        oracle          3882                     oracle@localhost.localdomain (Q004)
5649,18860                        oracle          3891                     oracle@localhost.localdomain (CJQ0)
57286,58632  ORDS_PUBLIC_USER     oracle          4175                     Oracle REST Data Services
5853,20873   ORDS_PUBLIC_USER     oracle          4179                     Oracle REST Data Services
5916,14520                        oracle          8232                     oracle@localhost.localdomain (GCR0)
6025,35234                        oracle          8235                     oracle@localhost.localdomain (W000)
61271,17974                       oracle          8241                     oracle@localhost.localdomain (GCR1)
6243,13620                        oracle          8267                     oracle@localhost.localdomain (M001)
6338,45731                        oracle          8273                     oracle@localhost.localdomain (M003)
64276,28467                       oracle          8276                     oracle@localhost.localdomain (M004)
6541,61869                        oracle          8304                     oracle@localhost.localdomain (GCR2)
66273,42070                       oracle          8313                     oracle@localhost.localdomain (W001)
6754,58456   SYS                  oracle          8510                     sqlplus@localhost.localdomain (TNS V1-V3)
6842,36219                        oracle          8907                     oracle@localhost.localdomain (M005)
69237,9496   SYS                  oracle          9666                     oracle@localhost.localdomain (J000)
7052,29429                        oracle          9685                     oracle@localhost.localdomain (J001)
71272,7991                        oracle          9786                     oracle@localhost.localdomain (J002)
72284,27835                       oracle          10371                    oracle@localhost.localdomain (M002)
73
7470 rows selected.
75SQL>

Purpose:

  • To retrieve and display detailed information about active database sessions, linking them to their corresponding operating system processes. This cross-layer visibility aids in understanding resource usage, troubleshooting performance issues, and identifying potential security concerns.

Breakdown:

Formatting Output:

*   `col "SID/SERIAL" format a10`, `col username format a20`, `col osuser format a15`, `col program format a50`: Adjusts column widths for enhanced readability.

Gathering Data from Multiple Views:

*   **Joining Views:** Combines data from two crucial database views:
    
    *   `v$session`: Provides details about active user sessions.
    *   `v$process`: Contains information about Oracle processes running on the operating system.
    
*   **Linking Sessions to Processes:** `Where s.paddr = p.addr`: Matches session addresses to process addresses, establishing the connection between database activity and underlying OS processes.

Selecting Relevant Information:

*   `select s.sid || ',' || s.serial# "SID/SERIAL", s.username, s.osuser, p.spid "OS PID", s.program`: Retrieves specific details:
    
    *   `s.sid || ',' || s.serial# "SID/SERIAL"`: Concatenates session ID and serial number for unique identification.
    *   `s.username`: Identifies the database user associated with each session.
    *   `s.osuser`: Indicates the operating system user who initiated the session.
    *   `p.spid "OS PID"`: Displays the operating system process ID (PID) for each session.
    *   `s.program`: Shows the program or application connected to the database session.

Ordering Results:

*   `order by to_number(p.spid)`: Arranges results based on OS PID, facilitating analysis by process.

Key Points:

  • Cross-Layer Visibility: Bridges database sessions with OS processes, providing a comprehensive view of resource usage and activity.
  • Detailed Information: Presents session IDs, users, OS users, PIDs, and programs, enabling thorough analysis.
  • Troubleshooting Aid: Helps identify sessions consuming excessive resources, causing performance issues, or potentially exhibiting unusual behavior.
  • Security Insights: Cross-referencing database users with OS users can reveal unauthorized access or potential vulnerabilities.

Insights and Explanations:

  • Performance Analysis:

    • Identifying sessions with high resource usage can pinpoint areas for optimization or troubleshooting.
  • Security Auditing:

    • Examining session-to-process mappings can uncover suspicious activity or unauthorized access attempts.
  • Resource Management:

    • Understanding session-process relationships aids in managing database resources effectively.
  • Customization:

    • The code can be modified to filter for specific users, programs, or PIDs based on analysis needs.
  • Integration: Consider incorporating this code into regular database monitoring and reporting processes for proactive performance and security management.

Posts in this series