Re: Spying on queries from application?

From: jodleren <sonnich_at_hot.ee>
Date: Fri, 5 Mar 2010 05:35:40 -0800 (PST)
Message-ID: <5bc2939f-6ef0-4c15-80ac-f83f3d6be028_at_q23g2000yqd.googlegroups.com>

On Mar 5, 1:37 pm, jodleren <sonn..._at_hot.ee> wrote:
> On Mar 5, 2:51 am, John Hurley <johnbhur..._at_sbcglobal.net> wrote:
>
>
>
> > On Mar 4, 1:31 pm, jodleren <sonn..._at_hot.ee> wrote:
>
> > snip
>
> > > I use Borland Database Explorer, and using
> > > alter system set timed_statistics=true
> > > then
>
> > >   ALTER SYSTEM SET
> > > EVENT='10046 trace name context forever, level 12'
> > > SCOPE=spfile;
>
> > Yeah that's kind of the starting point.  The idea is one approach is
> > to have a trigger look at all the new sessions  ( new connections )
> > and set the tracing for just the database session ( the odbc work )
> > that you want to get traced.
>
> > Do you have permission from IT management for what you are trying to
> > do?
>
> > If the DBA is dead are there other people in your organization that
> > might be useful in helping you get the information that you need?
>
> > At your experience level you really want to figure out how to get this
> > type of stuff done on a test system first ... please.
>
> I am on a test system, and I am the only user - besides test-intranet.
> And I can see 2 users, me and the storage programme. I have got all
> possible permitions possible and can access it all.
> I also see that it is not borland, but that does not stop me from
> reading it. I understand it.
>
> But let me return to my problem: I can get tracing from my database
> explorer, but not from my storage programme... why?

I have version 9.2, so dbmssupp.sql is not an option for me. But, this should be:

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;
/

it works, but the log files states "ORA-04098: trigger 'WISE.SET_TRACE' is invalid and failed re-validation"

but then this - using svrmgrl.exe - but I dont have that file...

oradebug set ospid 3048
oradebug set event '10046 trace name context forever, level 12'

then what can I do?

WBR
Sonnich Received on Fri Mar 05 2010 - 07:35:40 CST