Oracle Database Discussions

Hi All,

I want to capture SQL statements which belong to a specific user (in a certain time or past). Let's say that I am using HR user. I want to learn sql history of SCOTT. So, what can I do?

I just write the following block, I see that I can capture sql statements during the execution of the below code block, do you have another thoughts?

set serveroutput on;
declare
  type my_type is table of number index by varchar2(50);
  my_arr my_type;
  v_temp varchar2(50);
  ind varchar2(50);
begin


for i in 1..1000
loop
select nvl(sql_id, 'NULL') into v_temp from v$session where schemaname = 'SCOTT';
  my_arr(v_temp) := 1;
dbms_lock.sleep(0.01);
end loop;

ind := my_arr.first;

while ind is not null 
loop
  dbms_output.put_line(ind);
  ind := my_arr.next(ind);
end loop;


end;

Thanks in advance.