How to Find the Last SQL for INACTIVE Sessions in Oracle 10g (Practical DBA Guide)

When an Oracle database hits connection pressure (for example ORA-00020: maximum number of processes exceeded), DBAs usually see a sea of sessions — many of them INACTIVE. The next question is always:

“What SQL were these sessions running?”

In Oracle 10g, you can often identify the last SQL for an inactive session — but there are a few caveats you need to know. This guide shows the most reliable queries and a practical workflow to trace the source of session storms.

Why INACTIVE Sessions Matter

An INACTIVE session typically means Oracle is waiting for the client (common wait event: SQL*Net message from client). That’s normal for connection pools and idle users — but it becomes a problem when:

applications leak connections (open but never close),
connection pools are oversized or misconfigured,
batch jobs create many parallel sessions,
monitoring tools connect too frequently.

Oracle counts these sessions against your configured limits (PROCESSES / SESSIONS), so you can run out of capacity even when the database isn’t “busy”.

The Oracle 10g Reality Check (What You Can and Can’t See)

Oracle stores the “current” and “previous” SQL identifiers in the session view:

SQL_ID / SQL_HASH_VALUE → the SQL currently executing (often NULL for INACTIVE sessions)
PREV_SQL_ID / PREV_HASH_VALUE → the last SQL executed (often the best clue)

Important: Even if PREV_SQL_ID exists, the SQL text may not be available if it aged out of the shared pool (V$SQL).

So the goal is:

get the session’s SQL pointers
try to resolve them to SQL text
if SQL text is gone, use MODULE/ACTION/MACHINE/PROGRAM to identify the source

Step 1: List INACTIVE Sessions + Last SQL Pointers

Start by listing inactive sessions with both current and previous SQL references:

select s.sid, s.serial#, s.username, s.status,
       s.machine, s.program,
       s.sql_id, s.prev_sql_id,
       s.sql_hash_value, s.prev_hash_value,
       s.last_call_et
from v$session s
where s.username is not null
  and s.status = 'INACTIVE'
order by s.last_call_et desc;

Tip: LAST_CALL_ET shows how long (in seconds) the session has been idle since the last call.

Step 2: Pull the SQL Text (Best Effort)

Option A: SQL_ID method (preferred if populated)

This tries to return either current SQL or previous SQL:

select s.sid, s.serial#, s.username, s.machine, s.program,
       coalesce(s.sql_id, s.prev_sql_id) as last_sql_id,
       q.sql_text
from v$session s
left join v$sql q
  on q.sql_id = coalesce(s.sql_id, s.prev_sql_id)
where s.username is not null
  and s.status = 'INACTIVE'
order by s.last_call_et desc;

Option B: HASH_VALUE method (useful in some 10g environments)

If SQL_ID isn’t reliable in your system, use hash values:

select s.sid, s.serial#, s.username, s.machine, s.program,
       case
         when s.sql_hash_value <> 0 then s.sql_hash_value
         else s.prev_hash_value
       end as last_hash,
       q.sql_text
from v$session s
left join v$sql q
  on q.hash_value =
     case
       when s.sql_hash_value <> 0 then s.sql_hash_value
       else s.prev_hash_value
     end
where s.username is not null
  and s.status = 'INACTIVE'
order by s.last_call_et desc;

If SQL_TEXT is NULL:
It likely aged out of shared pool, or the session last executed PL/SQL where the “text” you expect isn’t visible as a single SQL statement.

Step 3: Get the Full SQL (Not Truncated)

V$SQL.SQL_TEXT can be truncated. For the full statement, use V$SQLTEXT_WITH_NEWLINES:

select t.sql_text
from v$session s
join v$sqltext_with_newlines t
  on t.sql_id = coalesce(s.sql_id, s.prev_sql_id)
where s.sid = :sid
order by t.piece;

(If you’re using hash values instead of SQL_ID, join by HASH_VALUE.)

Step 4: If SQL Text Is Gone, Identify the Source Anyway

When SQL is missing, the best move is identifying the origin of the sessions:

select machine, program, count(*) cnt
from v$session
where username is not null
group by machine, program
order by cnt desc;

Then drill into MODULE / ACTION (if the application sets it via DBMS_APPLICATION_INFO):

select s.sid, s.serial#, s.username, s.status,
       s.machine, s.program, s.module, s.action,
       s.event, s.wait_class, s.seconds_in_wait,
       s.last_call_et
from v$session s
where s.sid = :sid;

This often tells you:

which app server is leaking,
which scheduled job is spawning sessions,
which tool is generating excessive logins.

Common Scenarios (And What They Mean)

1) Many sessions waiting on `SQL*Net message from client`

Usually:

connection pool connections sitting idle,
users leaving sessions open,
application not closing connections properly.

2) Many sessions from one MACHINE/PROGRAM

Usually:

a single app server pool is mis-sized,
batch job creating hundreds of workers,
monitoring tool connecting too aggressively.

3) `PREV_SQL_ID` exists but `V$SQL` has no row

SQL text aged out (shared pool churn), or it was not captured in a way you can retrieve now.

Prevention: Don’t Just Raise PROCESSES and Hope

Yes, increasing PROCESSES helps — but if there’s a leak, you’ll simply hit the next ceiling. Consider these controls:

1) Enforce session caps (per user)

create profile app_limit limit sessions_per_user 20;
alter user APPUSER profile app_limit;

2) Kill true idle sessions safely (profile-based)

create profile idle_kill limit idle_time 30; -- minutes
alter user APPUSER profile idle_kill;

3) Fix pooling at the application layer

right-size pool max,
set idle timeout,
validate connections,
ensure every code path closes connections.

Quick DBA Workflow (My Go-To Checklist)

List inactive sessions + PREV SQL pointers
Resolve to SQL text using V$SQL
If missing, use MACHINE/PROGRAM/MODULE/ACTION
Find the dominant source and fix pooling/leaks
Only then raise PROCESSES with proper headroom

Final Thoughts

Oracle 10g gives you enough visibility to identify the last SQL for many inactive sessions — but the key is knowing where to look (PREV_SQL_ID) and when to switch tactics (source identification) if SQL text has aged out.

How to Find the Last SQL for INACTIVE Sessions in Oracle 10g (Practical DBA Guide)