Determining the statement being run by a user 2

[ChrisQuick]

Is there a way to exaimine the statement that a user last executed against oracle. The documentation makes reference to a "monitor" application that can be accessed from the file menu in SQL Worksheet. There is also supposed to be a script named UTLMONTR.SQL that can be run, but I can't find it.

chris_quick@urscorp.com
Geographic Information System (GIS), ASP, some Oracle

 

[mark06]

Try this ... (require DBA privileges)


column username format a20
column logon_time format a11
column load_time format a20

set recsep off
set lines 140

break on username on logon_time on sid on load_time

select substr(nvl(s.username,'Background'),1,20) username,
to_char(s.logon_time,'DD-MM HH24:MI') logon_time,
s.sid,
substr(a.first_load_time,1,20) load_time,
v.sql_text
from v$session s,
v$open_cursor o,
v$sqltext_with_newlines v,
v$sqlarea a
where s.sid=o.sid
and o.address=v.address
and v.address=a.address
order by s.sid,a.first_load_time,v.address,v.piece;

 

[ChrisQuick]

Thanks. That is exactly what I needed. chris_quick@urscorp.com
Geographic Information System (GIS), ASP, some Oracle

 

[mark06]

You're most welcome. I need to point out that the use of v$open_cursors means that some uncommitted transactions may have been paged out of memory - nothing's perfect in this world. As an afterthought you may find the following useful to show current locks on the database (it can take some time depending on your memory configuration) ...

select substr(nvl(s.username,'Background'),1,20) username,
s.sid,
l.type,
decode(lmode,3,'Exclusive',6,'Row Exclusive',4,'Share',lmode) lmode,
substr(p.program,1,26) program,
d.object_name,
d.object_type,
d.owner
from v$session s,
v$lock l,
v$process p,
v$locked_object o,
dba_objects d
where s.sid=l.sid
and s.paddr=p.addr
and s.sid(+)=o.session_id
and o.object_id=d.object_id
order by s.sid;

Cheers,
Mark.