1. How do you know what is the current SQL Running in the Database?
Solution: use the folowing script
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
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, sql_address, LAST_CALL_ET
from v$session where status = 'ACTIVE' and rawtohex(sql_address) <> '00' and username is not null 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); dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;/
Sharing real time knowledge,issues on Oracle Apps DBA and Oracle DBA
Subscribe to:
Post Comments (Atom)
Oracle EBS integration with Oracle IDCS for SSO
Oracle EBS integration with Oracle IDCS for SSO Oracle EBS SSO? Why is it so important? Oracle E-Business Suite is a widely used application...
-
Enabling TLS in Oracle Apps R12.2 Here we would be looking at the detailed steps for Enabling TLS in Oracle Apps R12.2 Introduction: ...
-
R12.2. Services Start and Stop Procedure All components - Application (Middle Tier) START $INST_TOP/admin/scripts adstrtal.s...
-
Apps password change routine in Release 12.2 E-Business Suite changed a little bit. We have now extra options to change password, as well ...
No comments:
Post a Comment