Re: SQL Trace

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 21 Aug 2003 17:16:51 +0100
Message-ID: <3f44f073$0$18494$ed9e5944@reading.news.pipex.net>

"Sha" <ssha53_at_singnet.com.sg> wrote in message news:bi2kic$3i8$1_at_reader01.singnet.com.sg...
> Hi there
>
> Can someone pls tell me how to activate the SQL Trace for a database ?
> How resource intensive is it ?

Taking the questions in reverse order

It isn't especially expensive in processing and memory usage, though activated for a whole database system all at once it will eat disk space like there is not tomorrow (and not give any useful info either)

To activate it

For your session

---

alter session set sql_trace=true|false
alter session set events '10046 trace name context forever, level 8|12' alter session set events '10046 trace name context off' - turns it off or -- better (gives more flexibility as to what info to collect) exec dbms_support.start_trace(true,true); exec dbms_support.stop_trace;

For some one else

---

exec dbms_system.set_sql_trace_in_session(<sid>,<serial#>,true) exec dbms_system.set_sql_trace_in_session(<sid>,<serial#>,false)

or -- better (gives more flexibility as to what info to collect) exec dbms_support.start_trace_in_session(<sid>,<serial#>,true,true) exec dbms_support.stop_trace_in_session(<sid>,<serial#>)

For System

---

alter system set sql_trace=true
set sql_trace=true in init.ora
set the 10046 event in init.ora

You can look up the parameters to dbms_support in the docs I believe. I know Received on Thu Aug 21 2003 - 11:16:51 CDT