Automatically trace any session that is active for over an hour???

[pcmosher]

Hello,

I have an Oracle 8.1.7.3.0 database on Win2000 that hangs periodically. I suspect that a developer is trying to run or test a procedure that causes the DB to hang. So far, I have been unable to identify the guilty party so I am looking for a way to trace any sessions that remain active for over an hour.
Is there a way to setup tracing to automatically trace any session that remains active for over an hour?
Since I don't know who or when this may occur again, I am hoping to find a way to identify the process that is running at the time the DB hangs. Any help is greatly appreciated.

Thanks,
Phil

[pnrdba]

Hi,
Here is the query i use basically. Try to run this every one hour and can find out who's doing what.

set pagesize 50
set linesize 132
select s.SID,substr(s.USERNAME,1,10) USERNAME,substr(s.OSUSER,1,10) OSUSER,q.sql_text
from v$session s, v$process p,v$sqltext q
where p.spid = &proc_unix
and q.address=s.sql_address
and s.paddr=p.addr
order by q.piece;

Correct me if i'm wrong.
thanks

[Calvin_Qiu]

you can use V$session and DBMS_JOB and DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to achieve it.