How to dump/trace/log all (ACTIVE) session SQL?

From: Chris Marquez <marquezemail_at_gmail.com>
Date: Mon, 16 Jan 2006 19:37:59 -0500
Message-ID: <d494e9760601161637g1b5b271u52f8fde6d465bbb4@mail.gmail.com>

Env:
Oracle 9205
RHEL3 I have situation (another post on this list) where we have severe spikes server load and db ACTIVE sessions (waits) spikes. When left alone the db and server spiral out of control (and hang), but before it gets really bad I can kill and stop stuff.

But I have no idea what (SQL) these session are doing...I know they are active and waiting ( "latch free" "library cache" waits) I don't want to trace the whole database all the time, but I would like to dump/trace/log the current SQL run (waiting) for all database sessions?

Any ideas?

PS I can NOT run SQL (v$sqltext) because my own session gets caught up as any ACTIVE waiting session.

Thanks,

--

Chris Marquez
Oracle DBA

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jan 16 2006 - 18:38:14 CST