Re: trace sql without session_id and serial#

On Jul 26, 6:59 am, Steve Robin <ocma..._at_gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Modify your trigger:

 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 tracefile_identifier = '''|| user||'''';
 execute immediate 'alter session set sql_trace=true';  end if;
 end;
 /

You will then see tracefile names with the traced username embedded.

David Fitzjarrell Received on Thu Jul 26 2007 - 08:26:25 CDT