catch sqls being executed

hi,

is there any way of catching the sqls being executed at a time or by a user? or is there any software that's out there that could do this? is it possible?

we're tried using spotlight on oralce but it doesn't seem to catch everything...

how about trace a session using dbms_system? Or use oradebug

I often use the Top Sessions app of the Enterprise Manager standard management pack.

Try BMC's Web DBA.

I also used spotlight , check top session or sql with data & time option it will give result. But Like you I am not sure it gives all.
Another OEM top session , all cursor will give you all sql open but it does not give which one is running.
I am also trying to find out which query is executed now, but not yet success.

If you want 3rd party software there is DBArtisan from Embarcadero.
It will show you what is the current SQL being executed by a particular session.

Hi,
for that purpose I use following script. It's not perfect but works ...

Code:

---

clear breaks
column logon format a22
column sid format 999
column machine format a15
column program format a25
set pagesize 200
set linesize 110
select sid, serial#, machine, program,
      to_char(logon_time,'yyyy.mm.dd hh24:mi:ss') logon,
      status, first_load_time, sql_text
  from v$session, v$sql
 where v$sql.address(+)=v$session.sql_address
  and status='ACTIVE'
 order by first_load_time

---

At end of the day no matter which 3rd party you use they all look at v$sql, v$sqlarea, v$sqltext_with_newlines, v$open_cursor, anyway
Nothing is magic ;)