Where is your Query stuck

If you know the process ID you can find the SID, SERIAL#, SQL_ID and other details using the following query: (process id can be taken from the unix TOP command)

-- To find the SID, username and SQL_ID associated with a process id taken from unix
select a.sid,a.serial#,a.username,a.status,a.sql_id
from v$session a,v$process b
where b.spid='&PROCESS_ID'
and a.paddr=b.addr
/

You can take a look at the actual explain_plan by executing the following

select operation,options,object_name,object_alias,object_type
from v$sql_plan where sql_id=''

Select from v$session_longops to find out where your query is

select qcsid,sid,username,opname,target,sofar,totalwork,units,start_time,
time_remaining,elapsed_seconds,message
from v$session_longops
order by qcsid
/