start a SQL*Plus session first, then start another session in Toad and compile the package that’s seemingly causing the hang.
When it hangs, switch back to the SQL*Plus session and execute the following:
where program = ‘Toad.exe’;
That should show you all the Toad sessions (which is why we are using SQL*Plus rather than another Toad!) that are in the database, and will show you what event they are waiting on.
If the STATE is WAITING, then you are in a wait currently, for the EVENT noted and have been for SECONDS_IN_WAIT. Otherwise, the session is not waiting.
If you see your session waiting on something other than “SQL*Net message from client”, you might get some useful data from the following script:
col spid for a10
col event for a35
col program for a25
col blocked_sid for a40
col blocking_sid for a15
set lines 300 trimspool on
set pages 3000
– Who is blocking other blockers?
select lpad(’ ', (level-1) * 2) || sid as blocked_sid, nvl(to_char(blocking_session), ‘<— CULPRIT’) as blocking_sid
where blocking_session is not null
or sid in (select blocking_session from v$session where blocking_session is not null)
start with blocking_session is null
connect by prior sid = blocking_session
order siblings by sid;
– And who else is blocked?
select p.spid, s.sid, s.blocking_session, s.program, s.event, s.p1, s.p2, s.p3, s.seconds_in_wait, state
from v$session s, v$process p
where p.addr = s.paddr
and (s.blocking_session is not null
or s.sid in (select blocking_session from v$session where blocking_session is not null))
order by s.sid;
The first part outputs the blocking and blocked sessions. Any session that is blocking, but not itself blocked is flagged as “CULPRIT” Look for your SID and see who is blocking it and why.
The second part gives a list of all the blocked and blocking sessions and what is going on with those.
Hopefully, you’ll be able to query V%PROCESS and V$SESSION in your SQL*Plus session.