Skip to content

Instantly share code, notes, and snippets.

@ozmoroz
Created January 1, 2018 10:43
Show Gist options
  • Save ozmoroz/4e9c2becfcb32e201f993f10cb061ac6 to your computer and use it in GitHub Desktop.
Save ozmoroz/4e9c2becfcb32e201f993f10cb061ac6 to your computer and use it in GitHub Desktop.
Show information about Oracle sessions
--
-- Show information about Oracle sessions
--
SELECT s.inst_id, s.osuser, gps.qcsid, p.spid, s.sid, s.serial#
, s.machine, s.schemaname, s.program, w.event AS wait_event
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr
LEFT OUTER JOIN gv$session_wait w ON w.sid = s.sid
LEFT OUTER JOIN gv$px_session gps ON gps.sid = s.sid AND gps.serial# = s.serial#
WHERE s.osuser != 'SYSTEM'
-- Show sessions on a particular instance of the RAC
-- AND s.inst_id = 1
-- Show sessions for the particular OS user
-- AND s.osuser LIKE 'sergey%'
-- If you know the session ID
-- AND s.sid = 11
-- If you know the OS process ID
-- AND pid = 22
-- If you know the name of the application accessing the database
-- AND Lower(s.program) LIKE 'sqlplus%'
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment