Re: Oracle Profile Question

On 18 Jul 2005 09:32:06 -0700, 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

First of all:
you should *solve problems*, not *fight symptoms*

Your assertion your procedure 'hangs' probably implies you have no exception handling at all ---> you should implement exception handling

However, you decided to embark further on your route to hell, and started fooling around with the PROFILE command. In doing so, did you notice you can ALTER an user to assign the user a different profile? Or did you, as usual, read the documentation only half, and you are now implementing something you don't actually understand.
If so: yes, you will end up in hell sooner or later, because you have created such a mess for yourself, you will never be able to resolve it.

--
Sybrand Bakker, Senior Oracle DBA