1. Active User Sessions
SELECT v.sid, v.serial#, v.username, v.osuser, v.machine, v.program, v.status, v.sql_id, v.event, v.seconds_in_wait, v.logon_time
FROM v$session v
WHERE v.type = 'USER'
AND v.status = 'ACTIVE'
ORDER BY v.seconds_in_wait DESC;
This is to find which App Server
process is creating Active Oracle sessions
2. Active Sessions with Current SQL
SELECT v.sid, v.serial#, v.username, v.machine, v.program, v.sql_id,
q.sql_text
FROM v$session v
LEFT JOIN v$sql q
ON v.sql_id = q.sql_id
WHERE v.status = 'ACTIVE'
AND v.username IS NOT NULL;
3. Active Sessions Count
SELECT username, COUNT(*) active_sessions
FROM v$session
WHERE status = 'ACTIVE'
AND username IS NOT NULL
GROUP BY username
ORDER BY active_sessions DESC;
This gives number of Active Oracle sessions grouped by DB username.
SELECT username, COUNT(*) AS total_sessions,
SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS
active_sessions,
SUM(CASE WHEN status = 'INACTIVE' THEN 1 ELSE 0 END) AS
inactive_sessions
FROM v$session
WHERE username IS NOT NULL
GROUP BY username
ORDER BY active_sessions DESC;
This is to check Total vs Active sessions.
4. Find Blocking Sessions
SELECT sid, serial#, username, blocking_session, event, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;
Below query is to find which application/server/process is causing the blocking.
SELECT sid, serial#, username, status, machine, program,
module, sql_id, event
FROM v$session
WHERE sid IN ( SELECT blocking_session FROM v$session WHERE
blocking_session IS NOT NULL );
5. Find All Active SQL Executions
SELECT v.sid, v.serial#,
v.username, v.sql_id, q.sql_text
FROM v$session v, v$sql q
WHERE v.sql_id = q.sql_id
AND v.status = 'ACTIVE'
AND v.username IS NOT NULL;
This is to find currently active Oracle sessions along with the SQL.
SELECT v.sid,
v.serial#, v.username, v.status, v.sql_id, v.module, v.action, v.program, v.machine, v.osuser,
q.sql_text
FROM v$session v
LEFT JOIN v$sql q
ON v.sql_id = q.sql_id
WHERE v.status = 'ACTIVE'
AND v.username IS NOT NULL
ORDER BY v.username, v.sid;
This is to identify which PeopleSoft process initiated each active database session.
(PeopleSoft Application Engine, Process Scheduler, or nVision process may have initiated the database session, but the current SQL could be something else.)
6. Active Sessions for a Specific PeopleSoft User
SELECT sid, serial#, username, machine, program, module, action,
client_identifier, sql_id
FROM v$session
WHERE username = 'SYSADM'
AND status = 'ACTIVE';
7. Check Sessions Using a Particular SQL_ID
SELECT sid, serial#, username, machine, sql_id
FROM v$session
WHERE sql_id = 'your_sql_id';
Replace 'your_sql_id' with the actual SQL ID
This is find all active or inactive sessions associated with a specific SQL_ID.
