OraFAQ Forum: Performance Tuning » list all active connections with sql statement used?

Home » RDBMS Server » Performance Tuning » list all active connections with sql statement used?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

list all active connections with sql statement used? [message #226686]

Mon, 26 March 2007 03:45

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,

Report message to a moderator

Re: list all active connections with sql statement used? [message #226689 is a reply to message #226686]

Mon, 26 March 2007 03:48

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

Try this one

Report message to a moderator

Re: list all active connections with sql statement used? [message #226714 is a reply to message #226689]

Mon, 26 March 2007 05:02

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,

Report message to a moderator

Re: list all active connections with sql statement used? [message #226716 is a reply to message #226714]

Mon, 26 March 2007 05:05

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Have a look at dbms_monitor.session_trace_disable.

Regards
Michel

Report message to a moderator

Re: list all active connections with sql statement used? [message #226721 is a reply to message #226716]

Mon, 26 March 2007 05:14

bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam

Member

This is great but do you have something for Oracle9i??

Thanks in Advance,

Report message to a moderator

Re: list all active connections with sql statement used? [message #226732 is a reply to message #226721]

Mon, 26 March 2007 06:11

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

sys.dbms_system.set_ev (l_sid, l_serial, 10046, 0, '');

Regards
Michel

Report message to a moderator

Re: list all active connections with sql statement used? [message #226761 is a reply to message #226732]

Mon, 26 March 2007 08:31

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,

Report message to a moderator

Re: list all active connections with sql statement used? [message #226770 is a reply to message #226761]

Mon, 26 March 2007 08:54

Michel Cadot
Messages: 68651
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

Report message to a moderator

Re: list all active connections with sql statement used? [message #226776 is a reply to message #226770]

Mon, 26 March 2007 09:01

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,

Report message to a moderator

Re: list all active connections with sql statement used? [message #226782 is a reply to message #226776]

Mon, 26 March 2007 09:14

Michel Cadot
Messages: 68651
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

Report message to a moderator

Previous Topic:	Oracle10g Performance question
Next Topic:	tuning needed for a query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 16 15:26:37 CDT 2024