Query to find out sql statement running by the active user

If you want to figure out which sql statement is running by the active user in database:

select v.sid,

       v.username,

       v.machine,

       v.server,

       v.osuser,

       v.process,

       s.optimizer_mode,

       s.hash_value,

       s.address,

       s.cpu_time,

       s.elapsed_time,

       s.sql_text

  from v$sqlarea s, v$session  v

 where v.sql_hash_value = s.hash_value

   and v.sql_address    = s.address

   and v.username is not null;

 

If you want to check sql statement for particular user:

select v.sid,

       v.username,

       v.machine,

       v.server,

       v.osuser,

       v.process,

       s.optimizer_mode,

       s.hash_value,

       s.address,

       s.cpu_time,

       s.elapsed_time,

       s.sql_text

  from v$sqlarea s, v$session  v

 where v.sql_hash_value = s.hash_value

   and v.sql_address    = s.address

   and v.username='SCOTT';

Comments