Re: trace sql without session_id and serial#

On Jul 26, 6:26 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Thanks Everyone....
Actually there was some privilege problem, but it is working now.

Thanks again Received on Fri Jul 27 2007 - 00:08:17 CDT