Get Last Command statement [message #18524] |
Mon, 04 February 2002 07:47 |
John Augusto Charnet
Messages: 1 Registered: February 2002
|
Junior Member |
|
|
Hello ... I need to retrieve the last command statement through a select. In this case it's not actually the last command statement, but the one before the last because the last will be the select that I've just done to find out whick statemente was executed before this select.
Thanks a lot...
Sincerely,
John
|
|
|
Re: Get Last Command statement [message #18535 is a reply to message #18524] |
Mon, 04 February 2002 16:55 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
based on code from asktom.oracle.com:
declare
x number;
begin
for x in
( select status, username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where --status = 'ACTIVE'
--and
rawtohex(sql_address) != '00'
and username is not null order by status, last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.status );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
Note - the report will only show stuff still in the shared pool.
|
|
|
|
|
|