| 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,
|
|
|
|
|
|
| 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,
|
|
|
|
|
|
|
|
|
|
| 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,
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
