Changing the WHAT Column of DBA_JOBS – Oracle 11g

materialized-vieworacleoracle-11g

I am using a job to update a materialized view.

I found that the job would lock itself if the exec dbms_refresh.refresh(''"<schema>"."<materialized_view_name>"''); was not committed and I would like to change the "what" column of the job to include a commit.

I face two possible solutions :

solution 1: a more complicated way is to recreate the job entirely, which I would like to avoid

Run SYS.DBMS_JOB.REMOVE(14133); COMMIT;

Recreate the whole job to now include the commit

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => 'exec dbms_refresh.refresh(''"<schema>"."<materialized_view_name>"'');COMMIT;'
   ,next_date => to_date('11-11-2016 00:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'trunc(sysdate)+1   '
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

solution 2: update the DBA_JOBS view directly by running a normal update statement on the view

update dba_jobs set what=(
select what from dba_jobs 
where job in ( '1','2','3','<etc>')
)||'COMMIT;'  
where job in ( '1','2','3','<etc>');

Is there a better solution than these two, is solution two possible ?

I'd like to avoid solution 1 as let's imply I don't have the code for the view at hand it would take more time plus let's imply I have hundreds to modify.

Best Answer

Really? DBMS_JOB has a procedure called CHANGE.

declare
  jobno number;
begin
  dbms_job.submit(jobno, 'begin null; end;');
end;
/
commit;

select job, what from user_jobs;

       JOB WHAT                         
---------- ------------------------------
         1 begin null; end;    

begin
  dbms_job.change(1, 'begin null; commit; end;', null, null);
end;
/
commit;

select job, what from user_jobs;

       JOB WHAT                         
---------- ------------------------------
         1 begin null; commit; end;
Related Question