Hi All,
Is there a SQL query that we can run to find the number of oracle processes used by individual users in the last 24 hours or more? The database version is 11.2.0.2 running on HP-UX.
When I run one of the below SQL I only get the current value but not earlier.Is there a way to get the same for last 24 hours? Thanks!
SET pages 1000 lines 200
break ON name
SELECT NVL(pname,'USERS') AS name,DECODE(NVL(pname,'USERS'),'USERS','ALL USERS','BACKGROUND') AS TYPE,COUNT(*) AS COUNT FROM v$process WHERE program!='PSEUDO' GROUP BY pname
UNION
SELECT 'USERS',username,COUNT(*) FROM v$session WHERE username IS NOT NULL GROUP BY username
ORDER BY name,TYPE,COUNT;
or
select username,count(*) AS "Max Processes" from v$session GROUP BY username;