Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Profile Question

Re: Oracle Profile Question

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 18 Jul 2005 09:43:42 -0700
Message-ID: <1121705026.936725@yasure>


amerar_at_iwc.net wrote:
> Hi All,
>
> We have a stored procedure that runs and grabs data from over a
> database link. Sometimes that link goes down because the remote
> database goes down.
>
> If this happens while our stroed procedure is running, it basically
> hangs, forever.
>
> I've been reading on how to maybe control the timeout on a query, and
> everything seems to point to creating a profile, and using the
> CPU_PER_CALL to control this.
>
> However, I do not want this profile in effect all the time, only during
> the duration of this job. And, this job does not run as SYSTEM.
>
> So, is there a way to select when to activate the profile? Or, would I
> have to change the script to have a DBA user log in, alter the profile,
> log in as the user, run the job as the user, and then log in as a DBA
> and alter the profile again?
>
> Thanks
>
> Arthur

SYSTEM has nothing to do with profiles and you certainly can use dynamic SQL to alter a user's profile dynamically. But I am more concerned with a database link that is not stable. Seems to me you are treating the symptom rather than the disease. And, to be quite honest, I am not sure your palliative medication will do anything at all.

Of course my first choice would be to fix the underlying problem. But my solution would be to write a job that validated the connection every X minutes and then killed sessions that were hung.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Mon Jul 18 2005 - 11:43:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US