Home » RDBMS Server » Performance Tuning » list all active connections with sql statement used?
list all active connections with sql statement used? [message #226686] Mon, 26 March 2007 03:45 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi All,
How can i list all active connections with sql statement used for each connection..i tried to connect v$sql with v$session to get the active sql query that executed by the user on the web application but i could not get the currently executed query..
i tried the following query but without any success:

SQL>select s.username, q.sql_text
from v$sql q, v$session s
where s.sql_address = q.address
and s.sql_hash_value = q.hash_value
and s.status = 'ACTIVE';


..is there any script available to do this ..


Thanks in Advance,
Re: list all active connections with sql statement used? [message #226689 is a reply to message #226686] Mon, 26 March 2007 03:48 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Try this one
Re: list all active connections with sql statement used? [message #226714 is a reply to message #226689] Mon, 26 March 2007 05:02 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thank you for your reply,
i create a trigger to capture all the users that connect currently to the databse and by using the trace file of each user i search for the sql query that seams to be executed at the current time..
the trigger is the following:

Create or replace trigger trigger_Username AFTER LOGON ON database
declare vuser varchar2(30);
begin
select sys_context('userenv','session_user')into vUser from dual;
if length(vuser )<> 0 THEN
execute immediate 'alter session set sql_trace = true';
null;
end if;
end;

and when i want to end the tracing i just turn sql_trace to false....is there is any other suggestions...

thanks in Advance,
Re: list all active connections with sql statement used? [message #226716 is a reply to message #226714] Mon, 26 March 2007 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at dbms_monitor.session_trace_disable.

Regards
Michel
Re: list all active connections with sql statement used? [message #226721 is a reply to message #226716] Mon, 26 March 2007 05:14 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
This is great but do you have something for Oracle9i??

Thanks in Advance,
Re: list all active connections with sql statement used? [message #226732 is a reply to message #226721] Mon, 26 March 2007 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sys.dbms_system.set_ev (l_sid, l_serial, 10046, 0, '');


Regards
Michel
Re: list all active connections with sql statement used? [message #226761 is a reply to message #226732] Mon, 26 March 2007 08:31 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thanks for your reply,
Well..using the dbms_system is a good solution off course Thanks for that..but i prefer using the following trigger which i found on the http://www.petefinnigan.com/ramblings/how_to_set_trace.htm:

SQL> create or replace trigger set_trace after logon on database
begin
if user not in ('SYS','SYSTEM') then
execute immediate 'alter session set timed_statistics=true';
execute immediate 'alter session set max_dump_file_size=unlimited';
execute immediate 'alter session set sql_trace=true';
end if;
exception
when others then
null;
end;
/
I think it is the same..don't you agree

Thanks in Advance,
Re: list all active connections with sql statement used? [message #226770 is a reply to message #226761] Mon, 26 March 2007 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The question was not about activating the trace, the OP already posted a logon trigger for this.
The question was how to stop the trace.
(And I assumed it is not by the current session itself even if the OP does not precise it).

Regards
Michel
Re: list all active connections with sql statement used? [message #226776 is a reply to message #226770] Mon, 26 March 2007 09:01 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thanks for your reply,
well on the same web page (http://www.petefinnigan.com/ramblings/how_to_set_trace.htm) there is a note:

[For a complete list of events that can be set look at the file $ORACLE_HOME/rdmbs/mesg/oraus.msg on Unix or Linux.
This file is not shipped on Windows systems. Also setting any event other that trace (10046) should not be done
without the guidance of Oracle support.]

and because windows is the OS that we used ,i tried to avoid using anything related to changing the events as Noted above..

Thanks in Advance,

Re: list all active connections with sql statement used? [message #226782 is a reply to message #226776] Mon, 26 March 2007 09:14 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Prior to 10g, dbms_system is the almost only way.

The alternative are worst: use dbms_support or oradebug (as sysdba).

Regards
Michel
Previous Topic: Oracle10g Performance question
Next Topic: tuning needed for a query
Goto Forum:
  


Current Time: Fri Dec 09 03:56:16 CST 2016

Total time taken to generate the page: 0.14312 seconds