Originally posted by ssmith
The question is, how do we trace the user/process that is causing this data surge?
Run the following at the time of the heavy transaction to find out the query, sid, oracle username, os username etc..

set serveroutput on
declare
x number;
begin
for x in
( select username||'('||a.sid||','||a.serial#||
') ospid = ' || a.process ||
' program = ' || a.program||' machine ='|| a.machine username,
to_char(a.LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
a.sql_address, a.LAST_CALL_ET, a.sql_hash_value, b.event,b.p1,b.p2, b.p3
from v$session a, v$session_wait b
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null and a.sid=b.sid order by 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.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET || ' ' || x.sql_hash_value);
dbms_output.put_line(substr( y.sql_text, 1, 250 ) );
dbms_output.put_line( x.event || ' ' || x.p1 || ' ' || x.p2 || ' ' || x.p3 );

end if;
end loop;
end loop;
end;
/