Database Session details

Following script can be used to get information like OS process ID, PGA allocation, SQL_ID, etc. associated with a database session. The script takes SID and SERIAL# as input.

-- Query to find the OS process details of a database session
-- Author: Abu Fazal Abbas
set lines 300;
col "DB User" for a10;
col "OS User" for a10;
col "SQL ID" for a25;
col "OS PID" for a10;
select p.spid as "OS PID",
p.pga_used_mem as "PGA Used",
p.pga_alloc_mem as "PGA Allocated",
p.pga_freeable_mem as "PGA Reclaimable",
s.sql_id as "SQL ID",
s.username as "DB User",
s.osuser as "OS User",
s.state "Session State"
from gv$process p
inner join gv$session s
on p.addr=s.paddr
and s.sid=&sid
and s.serial#=&serial

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed