Change user on scheduled task

Hans Andersen wrote:

If a user is deactivated, then scheduled tasks created by this user is also deactivated when the task hits execution time.

Can the ‘scheduled by’ user be changed?

 

I believe the only two options you have is to either create the schedule again with another user (preferably one that isn’t a real person that might leave) or update the db.

UPDATE batch_schedule_tab
SET username = 'IFSAPP’
WHERE schedule_id  = <schedule_id>

You may need to deactivate and reactivate the jobs after update.

 

I just copied the tasks. The copy changed the user to the active one and nothing else I believe.

Not a big deal after all.

@Hans Andersen 

Ahhh, you found the answer I was just going to mention, we had to do this quite a bit at the last upgrade, it really isn’t a ton of time.

It is interesting that there is a procedure batch_schedule_api.modify_username, but seemingly not possible to invoke from the client.

The following would loop all scheduled tasks for a given user and use the procedure to update Scheduled By to IFSAPP.

begin
  for r in (select *
              from ifsapp.batch_schedule
             where username = '*FROM_USER*'and BATCH_SCHEDULE_TYPE_DB = 'TASK') loop

    ifsapp.batch_schedule_api.Modify_Username(schedule_id_ => r.schedule_id,
                                              username_    => 'IFSAPP');
  end loop;
end;

begin
  for r in (select *
              from ifsapp.batch_schedule
             where username = '*FROM_USER*'and BATCH_SCHEDULE_TYPE_DB = 'TASK') loop

    ifsapp.batch_schedule_api.Modify_Username(schedule_id_ => r.schedule_id,
                                              username_    => 'IFSAPP');
  end loop;
end;

Ran this in SQl Developer and purred like a kitten.

As I’m leaving for pastures new - I’ve had to change over a number of tasks that I lazily created in my name.

I could have just left them - but that would have been cruel.