Re: DBMS_SCHEDULER problem.
From: Mladen Gogala <mgogala_at_yahoo.com>
Date: 29 Feb 2008 14:17:19 GMT
Message-ID: <47c813ee$0$1348$834e42db@reader.greatnowhere.com>
>
> For all those to recommend reading about SET_ATTRIBUTE, it doesn't help:
>
> 1 begin
> 2 dbms_scheduler.set_attribute(
> 3 name=>'GATHER_STATS_JOB',
> 4 attribute=>'repeat_interval',
> 5 value=>'freq=minutely;bymonthday=1'); 6* end;
> SQL> /
> begin
> *
> ERROR at line 1:
> ORA-27488: unable to set REPEAT_INTERVAL because SCHEDULE_NAME,
> EVENT_SPEC was/were already set
> ORA-06512: at "SYS.DBMS_ISCHED", line 2876 ORA-06512: at
> "SYS.DBMS_SCHEDULER", line 1847 ORA-06512: at line 2
>
>
> In other words, it is already set, it can't be changed without dropping
> and re-creating it.
Date: 29 Feb 2008 14:17:19 GMT
Message-ID: <47c813ee$0$1348$834e42db@reader.greatnowhere.com>
On Fri, 29 Feb 2008 13:41:00 +0000, Mladen Gogala wrote:
> On Fri, 29 Feb 2008 13:16:35 +0000, Mladen Gogala wrote:
>
>> DBMS_SCHEDULER contains no "alter job" procedure. I want to create new >> schedule for GATHER_STATS_JOB, without having to create a new job. As a >> matter of fact, DBMS_SCHEDULER has nothing like DBMS_JOB.CHANGE. I >> think I will create a new job using DBMS_JOB. Is anybody aware of >> anything I can do without having to drop and re-create the job?
>
> For all those to recommend reading about SET_ATTRIBUTE, it doesn't help:
>
> 1 begin
> 2 dbms_scheduler.set_attribute(
> 3 name=>'GATHER_STATS_JOB',
> 4 attribute=>'repeat_interval',
> 5 value=>'freq=minutely;bymonthday=1'); 6* end;
> SQL> /
> begin
> *
> ERROR at line 1:
> ORA-27488: unable to set REPEAT_INTERVAL because SCHEDULE_NAME,
> EVENT_SPEC was/were already set
> ORA-06512: at "SYS.DBMS_ISCHED", line 2876 ORA-06512: at
> "SYS.DBMS_SCHEDULER", line 1847 ORA-06512: at line 2
>
>
> In other words, it is already set, it can't be changed without dropping
> and re-creating it.
Problem was solved by DBMS_SCHEDULER.ATTRIBUTE_SET_NULL(
name=> 'GATHER_STATS_JOB', attribute => 'SCHEDULE_NAME');Awkward as heck. I still believe that a call to modify job directly would be very useful.
-- Mladen Gogala http://mgogala.freehostia.comReceived on Fri Feb 29 2008 - 08:17:19 CST