Re: How to detect which session is being trace?

Hi!

> I recently come to realise that there are lots of trace file in the
> udump directory. Even when the SQL_TRACE is set to FALSE in the
> init.ora file, I realise that some user can change their session
> sql_trace to true.

Are you sure that these trace files in your udump are a result of SQL_TRACE? There are several other occasions when trace files get written to udump by Oracle server processes.

> Is there any way I can detect what session is being trace? Also, may I
> know what privilege is needed to allow user to issue 'alter session'
> or 'alter system'.

Are you using MTS/shared server? That may be the cause why you have several trace files, even though only one session is tracing. If you want to disable your tracing systemwide, just use dbms_system.set_sql_trace_in_session to false for every session (or dbms_support.stop_trace_in_session). AFAIK, there is no easy way to check sql_trace's value for other sessions (maybe with oradebug dumpvar). There is a dbms_system.read_ev procedure with which you can check the level for sql_trace (event 10046), but this works only for current session.

For altering session and altering system you need "alter session" and "alter system" privileges respectfully. Note that CONNECT role has alter session privilege by default, so this might be your problem. I do advise against using these default roles like CONNECT, RESOURCE and DBA, just grant these privileges to your users what they do need for their business function.

>
> I beleive there is also some dbms package to allow user to set trace.
> I wonder what privilege does a user needs to issue this package call.

Yep, dbms_system and dbms_support. Also execute catalog role and execute any procedure privileges might be relevant here, depending on version and parameter settings.

Tanel.
http://integrid.info Received on Sat Dec 06 2003 - 05:01:55 CST