Spying on queries from application?

#11

On Mar 5, 2:51*am, John Hurley <johnbhur... (AT) sbcglobal (DOT) net> wrote:

Quote:

On Mar 4, 1:31*pm, jodleren <sonn... (AT) hot (DOT) 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?

#12

On Mar 5, 1:37*pm, jodleren <sonn... (AT) hot (DOT) ee> wrote:

Quote:

On Mar 5, 2:51*am, John Hurley <johnbhur... (AT) sbcglobal (DOT) net> wrote:

On Mar 4, 1:31*pm, jodleren <sonn... (AT) hot (DOT) 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

#13

On Fri, 05 Mar 2010 03:37:51 -0800, jodleren wrote:

Quote:

But let me return to my problem: I can get tracing from my database
explorer, but not from my storage programme... why?

The answer to that question depends on the error message you're getting
when you try to do that.

--
http://mgogala.byethost5.com

#14

On Mar 5, 5:42*pm, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:

Quote:

On Fri, 05 Mar 2010 03:37:51 -0800, jodleren wrote:
But let me return to my problem: I can get tracing from my database
explorer, but not from my storage programme... why?

The answer to that question depends on the error message you're getting
when you try to do that.

--http://mgogala.byethost5.com

That was:

*** 2010-03-05 13:59:31.268
*** SESSION ID

17.16450) 2010-03-05 13:59:31.268
Skipped error 4098 during the execution of WISE.SET_TRACE
*** 2010-03-05 13:59:31.268
ksedmp: internal or fatal error
ORA-04098: trigger 'WISE.SET_TRACE' is invalid and failed re-
validation

which really does not explain much...

I also found an tried this:

create or replace trigger wise.sql_trc
after logon
on wise.schema
begin
execute immediate 'alter session set sql_trace=true';
end;
/

Here the report is:

*** SESSION ID

9.32137) 2010-03-05 18:02:42.781
Skipped error 604 during the execution of WISE.SQL_TRC
*** 2010-03-05 18:02:42.781
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 2

- I am the admin of the DB, so I wonder what else I have to do...

WBR
Sonnich

#15

On Fri, 05 Mar 2010 08:11:13 -0800, jodleren wrote:

Quote:

ORA-01031: insufficient privileges

If I was out and could respond to usenet messages, what would the above
line suggest to you?

--
http://mgogala.byethost5.com

#16

On Mar 5, 6:13*pm, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:

Quote:

On Fri, 05 Mar 2010 08:11:13 -0800, jodleren wrote:
ORA-01031: insufficient privileges

If I was out and could respond to usenet messages, what would the above
line suggest to you?

Good question - that I have to set that somewhere. I already tracked
the net to find it, but without luck

I could be for that user at that time too - or me setting it up.
Still, I cannot find which privileges or where to set them,

See the problem is that I am searching for something for which I do
not (yet) know the right words - that makes it a bit hard. Like
someone in a previous dicussion suggested svrmgrl - it took some time
for me to figure that is an exe file, which I do have anyway.

Sonnich

#17

On Mar 5, 6:13*pm, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:

Quote:

On Fri, 05 Mar 2010 08:11:13 -0800, jodleren wrote:
ORA-01031: insufficient privileges

If I was out and could respond to usenet messages, what would the above
line suggest to you?

Since I am ranning out of time I granted all privileges to the user,
and I get the selects there - nice - but the inserts, or calls to
procedures are still missing.

#18

On Mar 5, 8:35*am, jodleren <sonn... (AT) hot (DOT) ee> wrote:

snip

Quote:

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"

What do you mean "it works?" ...

Normally system level triggers are not placed in user schemas.
( Check out your message about "WISE.SET_TRACE" aka
"SCHEMA.TRIGGER_NAME" ).

Did you install the trigger as SYSDBA?