Materialized view refresh problem

From: wissem elkhlifi <wissem_at_oracle-great.com>
Date: Thu, 30 Jun 2011 09:16:33 +0200
Message-ID: <BANLkTinzfYqg7A_WXsjRUvQRpkd7fSYnkQ_at_mail.gmail.com>

>
> 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
>
>

-- 

--------------------------------------

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 30 2011 - 02:16:33 CDT