What is the tool to monitor all sql executed say last a couple of hours?
I have looked at TopSQL but this doesn't produce what i wanted.

Thanks in advance.

On Thu, 17 Nov 2005 21:01:37 GMT, "Jackson" <Jackson@dddd.com> wrote:

>What is the tool to monitor all sql executed say last a couple of hours?
>I have looked at TopSQL but this doesn't produce what i wanted.
>
>Thanks in advance.
>
sql_trace=true in init.ora

........
We use Oracle 8.1.7.4 and 9.2.0.6/7 on Solaris 2.7 and RH4
remove NSPAM to email

Can I do this from Enterpise Manager?
After set to init.ora What should i do go to TopSQL or somewhere else?

"NetComrade" <netcomradeNSPAM@bookexchange.net> wrote in message
news:437cfa6f.105666593@localhost...
> On Thu, 17 Nov 2005 21:01:37 GMT, "Jackson" <Jackson@dddd.com> wrote:
>
> >What is the tool to monitor all sql executed say last a couple of hours?
> >I have looked at TopSQL but this doesn't produce what i wanted.
> >
> >Thanks in advance.
> >
> sql_trace=true in init.ora
>
> .......
> We use Oracle 8.1.7.4 and 9.2.0.6/7 on Solaris 2.7 and RH4
> remove NSPAM to email

Jackson wrote:
> Can I do this from Enterpise Manager?
> After set to init.ora What should i do go to TopSQL or somewhere else?

All sql will be logged on the server in
$ORACLE_BASE/admin/<SID>/[udump|bdump]

netcomrade@netscape.net wrote in
news:1132350270.419767.96480@f14g2000cwb.googlegro ups.com:

> Jackson wrote:
>> Can I do this from Enterpise Manager?
>> After set to init.ora What should i do go to TopSQL or somewhere else?
>
> All sql will be logged on the server in
> $ORACLE_BASE/admin/<SID>/[udump|bdump]
>
>

Are you real sure about your statement?
Even SELECT statements?
I do not think so; unless of course you previously enabled SQL_TRACE at the
instance level.

"Jackson" <Jackson@dddd.com> wrote in message
news:Rc6ff.202491$ir4.28073@edtnps90...
> What is the tool to monitor all sql executed say last a couple of hours?
> I have looked at TopSQL but this doesn't produce what i wanted.
>
> Thanks in advance.
>
>

Use an after logon trigger (installed with schema SYS) and enable/disable
this trigger whenever you want to trace sessions. Warning : this could
generate a large amount of trace files !

Example :

create or replace trigger trace_sessions
after logon on database
begin
execute immediate 'alter session set sql_trace=true';
end;
/

alter trigger trace_sessions enable/disable;


HTH
Matthias

On Fri, 18 Nov 2005 22:58:01 GMT, "IANAL_VISTA"
<IANAL_Vista@hotmail.com> wrote:

>Are you real sure about your statement?
>Even SELECT statements?
>I do not think so; unless of course you previously enabled SQL_TRACE at the
>instance level.

OP asked how he can log 'all SQL'.. I advised to set it in init.ora
(so yes, at the instance level). OP didn't specify why he needs to
collect 'all sql'. When setting sql_trace=true in init.ora all trace
files will be on OS side, I am not aware if Enterprise Manager will
collect this info.

I forgot to mention that tracing all files will have decent overhead,
and should only be done for short periods of time...

OP might want to look into logon triggers (and setting trace on
session level), so that he/she wouldn't have to bounce the db to
disable.
........
We use Oracle 8.1.7.4 and 9.2.0.6/7 on Solaris 2.7 and RH4
remove NSPAM to email