MV Job change in dba_jobs
From: Sanjay Mishra <"Sanjay>
Date: Thu, 19 Apr 2018 22:55:24 +0000 (UTC)
Message-ID: <1073126984.1695269.1524178524548_at_mail.yahoo.com>
Date: Thu, 19 Apr 2018 22:55:24 +0000 (UTC)
Message-ID: <1073126984.1695269.1524178524548_at_mail.yahoo.com>
I had created MV which by default goes to dba_jobs with no other parameters. I need to change the and add ATOMIC_REFRESH to avoid Delete operation and so will be adding new entry and will remove old one. I dont have access to schema and so will be using dbms_ijob
select what from dba_jobs;what---------------------------------------dbms_refresh.refresh('"RECODBA"."TEST_MV"');
declarejob_num number;nlsvar varchar2(4000);envvar raw(32);beginselect nls_env,misc_env into nlsvar,envvar from dba_jobs where rownum<2 and nls_env is not null and misc_env is not null ;select max(job)+1 into job_num from dba_jobs;
sys.dbms_ijob.submit(job=>job_num,luser=>' RECODBA',puser=>' RECODBA',cuser=>'RECODBA', what=>'dbms_mview.refresh(list=>''TEST_MV'',method=>''C'',atomic_refresh=>FALSE)' ,next_date=>TRUNC(SYSDATE+1)+1/24, interval=>'TRUNC(SYSDATE+1)+1/24', broken=>FALSE,nlsenv=>nlsvar,env=>envvar);
dbms_output.put_line(job_num);end;/
select what from dba_jobs;what---------------------------------------dbms_refresh.refresh('"RECODBA"."TEST_MV"');dbms_mview.refresh(list=>'TEST_MV',method=>'C',atomic_refresh=>FALSE,parallelism=>10,out_of_place=>true)
When tried to execute it, it is giving error and Alert log showsThu Apr 19 14:03:59 2018Errors in file /u01/app/oracle/diag/rdbms/actrepd/actrepd/trace/actrepd_ora_14326.trc:ORA-12012: error on auto execute of job 42ORA-06550: line 1, column 213:PLS-00103: Encountered the symbol "" when expecting one of the following: := . ( % ;The symbol ";" was substituted for "" to continue.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 20 2018 - 00:55:24 CEST