Re: alter session set sql_trace inside a procedure

"FC" <flavio_at_tin.it> wrote in message
news:%Y63c.20311$O31.932460_at_news4.tin.it...
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:404c4eac$0$19222$ed9e5944_at_reading.news.pipex.net...
> > forgot to mention
> http://www.petefinnigan.com/ramblings/how_to_set_trace.htm
> > which is excellent.
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
>
>
> Thank you for the link Niall.
> Regarding DBMS_SUPPORT, I cannot install the package as I am not the DBA
of
> the database where these tests are carried out, I am just "the" developer.
> I had some thoughts about what I stated at the beginning of this thread
and
> more and more I find that it could be advisable to instrument the code in
> such a way that tracing can be turned on without tampering with sql
scripts.

I wish that more developers would think like this. As far as the install goes, then I agree with Daniel that you should request that the dba either install it or grant you execute access on an alternative procedure (eg DBMS_SYSTEM). As a DBA I'd be far more worried about granting alter session than execute on DBMS_.... or select on v_$session.

> Today I found myself in one of those situations where you need to enable
> tracing before launching a series of batch processes and you cannot touch
> the sql scripts because they are located on a machine where you don't even
> have access to the command line.
> I don't have access to DBA stuff either, so
> DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION is out of question.
> So I thought it could be great to have the possibility of enabling sql
trace
> inside the procedure, depending on a global variable or via dbms_alert, to
> avoid adding parameters to the existing procedures.
> I like the dbms_alert approach because it's very easy to implement and
gives
> you the ability to turn on tracing selectively or extensively by means of
a
> simple message that can be broadcast in advance.
> Next I need to convince the sysadmin to make trace files available, but
this
> is a different story...

also consider using DBMS_APPLICATION_INFO to set info about the app in v$session.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK