Re: dbms_job - dbms_stats

On Thu, 27 Mar 2003 10:06:44 +0800, Connor McDonald <connor_mcdonald_at_yahoo.com> wrote:

>Ed Stevens wrote:
>>
>> Platform: Oracle 8.1.7 on Solaris
>>
>> I'm trying to set up a dbms_job to execute
>> dbms_stats.gather_schema_stats. I've actually got it working, but am
>> a bit puzzled by one thing. I found that I had to submit the job
>> (exec dbms_job.submit) while logged on as the owner of the schema
>> whose stats were being gathered. If I submitted the job while logged
>> on as SYSTEM, the job would submit, but its execution would fail. Is
>> this to be expected, or did I miss something in the docs?
>
>Probably SYSTEM has its 'analyze any table', 'select any table' privs
>granted through the DBA role only, and thus no longer has that power
>when running through a procedure.
>
>hth
>connor

Ah! I guess I should have been able to figure that out, but it goes back to my most fundamental problem -- I've not had 'x' years experience with Oracle, but have had 1 year's experience 'x' times. I'm just now getting the opportunity to start dealing with procedures and roles in any sort of 'intamate' way!

So, what's best practice here? I would assume *not* doing an end-run around roles by granting the privilege directly to SYSTEM. In the short term, I simply granted 'create session' to the schema owner and submitted the job under his log-on. Received on Thu Mar 27 2003 - 07:44:51 CST