Re: utl_recomp grants

Adding the list back in for other useful feedback and opinions.

I'm somewhat sceptical of these sorts of solutions since often there's an underlying reason why an object is invalid which recompilation won't fix and therefore human intervention is required. In addition, the scope of your solution recompile *all* invalid objects doesn't match the alert " *this* object is invalid" - surely a better approach would be to attempt to fix the particular object that is invalid.

On Wed, Apr 25, 2018 at 9:20 AM, Suraj ramesh <surajmalliramesh_at_gmail.com> wrote:

> Hi Niall,
>
> We have a monitoring tool which will find invalid object in the DB, based
> on that ticket will be generated. To resolve this putting a script,connect
> to the DB using test user, find invalid objects, if yes then run EXECUTE
> UTL_RECOMP.RECOMP_PARALLEL() to validate the objects.Since the scripts
> runs from centralized server thought to provide grant only to execute this
> procedure to the test user rather than providing sysdba privilege to the
> test user. From the link which you have set it was mentioned to run this
> connected as sysdba,will be there any issue if we run connecting test user?
>
> Regards
> Suraj.
>
> On Wed, Apr 25, 2018 at 1:31 PM, Niall Litchfield <
> niall.litchfield_at_gmail.com> wrote:
>
>> Can I ask what the objective is here? The documentation is pretty clear
>> as to the intended use, and the requirement to be connected AS SYSDBA.
>> https://docs.oracle.com/database/121/ARPLS/u_recomp.htm#ARPLS71434
>>
>>
>>
>> On Tue, Apr 24, 2018 at 10:05 AM, Suraj ramesh <
>> surajmalliramesh_at_gmail.com> wrote:
>>
>>> Hello,
>>>
>>> DB Version: 12.1.0.2
>>>
>>> I am looking whether its possible to run EXECUTE
>>> UTL_RECOMP.RECOMP_PARALLEL() command by any other DB user apart from Sys.
>>>
>>> I have tried the below and ended up with error.
>>>
>>> grant execute on sys.UTL_RECOMP to TEST;
>>>
>>> Connect test/test
>>>
>>> EXECUTE UTL_RECOMP.RECOMP_PARALLEL(4);
>>>
>>> BEGIN UTL_RECOMP.RECOMP_PARALLEL(4); END;
>>>
>>> *
>>> ERROR at line 1:
>>> ORA-06550: line 1, column 7:
>>> PLS-00201: identifier 'UTL_RECOMP.RECOMP_PARALLEL' must be declared
>>> ORA-06550: line 1, column 7:
>>> PL/SQL: Statement ignored
>>>
>>>
>>> Regards
>>> Suraj.
>>>
>>
>>
>>
>> --
>> Niall Litchfield
>> Oracle DBA
>> http://www.orawin.info
>>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l