2

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.

3
  • Trying to change the way the job is executed
    – A_V
    Nov 10, 2016 at 16:10
  • NEVER try to update the dba_ or v$ views... in addition: dbms_scheduler replaced dbms_job in 11g, so I would recommend switching to dbms_scheduler anyways. Also, since the mview is already in a refresh group, why can't you just set the refresh interval from that interface instead of needing to refresh via dbms_job anyways (unless you need some odd refresh schedule that isn't supported through dbms_refresh (but that doesn't appear to be the case here since you are just refreshing once a day). dbms_scheduler jobs can be modified using: exec dbms_scheduler.set_attribute. Nov 10, 2016 at 20:09
  • 1
    Solution 1 is more appropriate. Don't do solution 2
    – Raj
    Nov 10, 2016 at 22:00

1 Answer 1

2

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;  
1
  • Oh, it was THAT easy. Off to write a query-generating query to batch these with dbms_job.change thanks balazs
    – A_V
    Nov 14, 2016 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.