> > Hi all, > > We are facing a problem with 11gR2 ; It's a fresh install. > > When,The complete refresh of a single materialized view does a truncate if > we run follwing refresh command > > exec dbms_mview.refresh('COUNTER','C',atomic_refresh=>FALSE) //i.e without > the program > > But, when we create a dbms_scheduler.create_program and than use it to > create the scheduled job to refresh the Materialized view than the table is > deleted instead of truncate. > > BEGIN > DBMS_SCHEDULER.RUN_JOB( > job_name => 'envref_job' > ); > END; > / > > We traced it and we have seen a delete instead of a truncate. > > But if I run the refresh with the scheduler program than it does a delete > and not truncate. > > So it looks like when we create a dbms_scheduler.create_program and than > use it to create the scheduled job than the table is deleted instead of > truncate. > > I still have to run the session trace for the job where 4 mviews are > refreshed together to see the behaviour. > > Here is the scheduler script for counter. > > BEGIN > dbms_scheduler.create_program( > program_name => 'das_mvref_prg', > program_type => 'stored_procedure', > program_action => 'dbms_mview.refresh', > number_of_arguments => 3, > enabled=> FALSE > ); > dbms_scheduler.define_program_ > argument( > program_name => 'das_mvref_prg', > argument_name => 'list', > argument_position => 1, > argument_type=>'VARCHAR2'); > > dbms_scheduler.define_program_argument( > program_name => 'das_mvref_prg', > argument_name => 'method', > argument_position => 2, > argument_type=>'VARCHAR2'); > > dbms_scheduler.define_program_argument( > program_name => 'das_mvref_prg', > argument_name => 'atomic_refresh', > argument_position => 3, > argument_type=>'VARCHAR2'); > dbms_scheduler.enable('das_mvref_prg'); > > dbms_scheduler.create_program( > program_name => 'envref_prg', > program_type => 'stored_procedure', > program_action => 'dbms_mview.refresh', > number_of_arguments => 3, > enabled=> FALSE > ); > dbms_scheduler.define_program_argument( > program_name => 'envref_prg', > argument_name => 'list', > argument_position => 1, > argument_type=>'VARCHAR2'); > > dbms_scheduler.define_program_argument( > program_name => 'envref_prg', > argument_name => 'method', > argument_position => 2, > argument_type=>'VARCHAR2'); > > dbms_scheduler.define_program_argument( > program_name => 'envref_prg', > argument_name => 'atomic_refresh', > argument_position => 3, > argument_type=>'VARCHAR2'); > dbms_scheduler.enable('envref_prg'); > END; > / > BEGIN > dbms_scheduler.create_job > (job_name => 'das_mvref_job', > program_name => 'das_mvref_prg', > start_date => systimestamp, > repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=1; BYSECOND=0' > ); > DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE > (job_name => 'das_mvref_job', > argument_position => 1, > argument_value => > 'DEVICECONTACT,DEVICEMATVIEW,CUSTOMERMATVIEW,GEOLOCATIONMATVIEW,DEVICEVARIABLEMATVIEW' > ); > > DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE > (job_name => 'das_mvref_job', > argument_position => 2, > argument_value => 'C' > ); > DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE > (job_name => 'das_mvref_job', > argument_position => 3, > argument_value => 'FALSE' > ); > dbms_scheduler.enable('das_mvref_job'); > > dbms_scheduler.create_job > (job_name => 'envref_job', > program_name => 'envref_prg', > start_date => systimestamp, > repeat_interval => 'FREQ=MINUTELY;interval=5' > ); > DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE > (job_name => 'envref_job', > argument_position => 1, > argument_value => 'COUNTER' > ); > > DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE > (job_name => 'envref_job', > argument_position => 2, > argument_value => 'F' > ); > DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE > (job_name => 'envref_job', > argument_position => 3, > argument_value => 'FALSE' > ); > dbms_scheduler.enable('envref_job'); > END; > / > > Thank you > > -- --------------------------------------