Find current running SQL Queries in Oracle

Check the current running SQL Queries in Oracle

Check the current running SQL statements

set line 200 pages 200
select s.module,sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address = s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username is not null
order by s.sid,t.piece;

Find the Current SQL with SQL ID, Username and hash value , elapsed time, sql Text columns

set line 200 pages 200
col username for a20
select s.sid, s.username,optimizer_mode,hash_value,
address,cpu_time,elapsed_time,sql_text
from v$sqlarea q, v$session s
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.username is not null;

Find SID and Serial# for the current SQL running

select s.sid,s.serial#,s.username,s.sql_id,q.sql_text
from v$sqlarea q ,v$session s
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.username is not null;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.