Re: trace sql without session_id and serial#

On Jul 26, 2:03 pm, gazzag <gar..._at_jamms.org> wrote:
> On 26 Jul, 08:14, Steve Robin <ocma..._at_gmail.com> wrote:
>
> > My current database so many users are connected. SQL trace is off. I
> > need to trace a developer session. I just have osname and username.
> > Session is not connected right now. As soon as he connects some sql
> > fired from that session. I know how trace it after he connects I can
> > see session info from v$session but without knowing session id and
> > serial#, how can I trace with osuser and username only.
>
> > I cann't restart database, because so many people are working on it.
>
> > Oracle Database : 9.2.0.8
> > OS : Windows 2000
>
> Logon trigger:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5...
>
> HTH
>
> -g

I did the same..... but it is not working, following is my code.

create or replace trigger logon_audit_trigger AFTER LOGON ON DATABASE
declare
ALLOW number;
BEGIN
select count(1) into allow from trace_user where upper(osuser)=upper(sys_context('USERENV', 'OS_USER')) and upper(useris)=upper(user);
if allow <> 0 then
execute immediate 'alter session set sql_trace=true'; end if;
end;
/

or I am not able to find whether tracing is getting enabled or not. how to check session's parameters. Received on Thu Jul 26 2007 - 06:59:16 CDT