Re: alter session set sql_trace inside a procedure

From: FC <flavio_at_tin.it>
Date: Tue, 09 Mar 2004 18:26:17 GMT
Message-ID: <d%n3c.24932$O31.1090678@news4.tin.it>

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:404d9d37$0$22391$ed9e5944_at_reading.news.pipex.net...
> "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.
>

Well, I haven't made much progress on this issue today, I asked for a script running chmod on trace files with little success and got a reply telling that I'd rather ask the sysadmin for specific files. I mean, it can be done, but what a waste of time (mine and theirs), moreover it takes hours to process a request, so I feel almost like 20+ years ago when I submitted a batch job in the evening and collected a printed report the day after...Jeez!
This is what they call "near real time" probably.

>

> > 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.

>

Yep, that's was my first action on the to do list, actually I am also handling a sort of call stack of this stuff because I have nested procedures and every time a procedure exits, I want to get the previous module/action back.

Bye,
Flavio Received on Tue Mar 09 2004 - 12:26:17 CST