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.
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.
for that purpose I use following script. It's not perfect but works ...
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
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
Click Here to Expand Forum to Full Width