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.