Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Profile Question
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