Pavan DBA's Blog

The DBA Knowledge Store

Query to find Active SQL’s in database

Posted by Pavan DBA on March 8, 2012

set feedback off

set serveroutput on size 9999

column username format a20

column sql_text format a55 word_wrapped


for x in

(select 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,



from v$session

where status = ‘ACTIVE’

and rawtohex(sql_address) <> ’00’

and username is not null ) loop

for y in (select sql_text

from v$sqlarea

where address = x.sql_address ) 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 || ‘ SQL#=’ || x.sql_hash_value);

dbms_output.put_line( substr( y.sql_text, 1, 250 ) );

end if;

end loop;

end loop;




Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: