DBMS Scheduler for MV Refresh

[Maven4Champ]

Greetings,

I have a question for all you Oracle 10g2 experts out there. I am trying to create a DBMS Scheduler Job to refresh a list of materialized views in our database. Because our DBAs want a more localized mechanism within Oracle to do the refresh (instead of a script used by operators or a .bat file to execute SQLPLUS and do the refreshes for us), I have opted for the DBMS SCHEDULER functionality in Oracle10g.

Below is my syntax. However I am receiving a permissions error - more than likely because my account doesn't have permissions to create the job which is fine (for now).

I have made this generic enough where it can be re-used. Is there someone out there with sys permissions that can fire this off for an MV of their own and verify that it works.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    JOB_NAME        => 'MY_MV_REFRESH',
    JOB_TYPE        => 'PLSQL_BLOCK',
	JOB_ACTION		=> 'BEGIN mv_refresh_job(''EXEC DBMS_MVIEW.REFRESH(''MV_NAME_HERE'', ''C'')''); END;',
    START_DATE      => SYSTIMESTAMP,
    REPEAT_INTERVAL => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    END_DATE        => NULL,
    ENABLED         => TRUE,
    COMMENTS        => 'Refreshes Materialized Views on a Scheduled Basis (12:00AM each day).');
END;
/

As well, not sure how it will handle multiple MVs and if it goes in order of how I list them - i.e. MV_NAME_1 and MV_NAME_2 won't be kicked off in paralell but rather one after the other? Also where to insert the delimeter when I want to add more MVs to the mv_refresh_job statement?

Any advice on if I am proceeding correctly with this would be greatly appreciated.

Thanks!

 

[Turkbear]

Hi,
If the MV's underlying data does not change too frequenty ( or even it does but a slight delay in commit is OK), why not use the ON COMMIT refresh option and let the need dictate the refresh?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Maven4Champ]

Turkbear,

We looked into this solution and decided that due to how often the data is updated in the database and also for balancing load (we have 95 views to refresh at this moment) we weighed the options and felt the DBMS_SCHEDULER would be most beneficial also because the data may have to be refreshed only once or in some cases twice a day but prior to operating/business hours depending on the location of the end-user.

 

[Beilstwh]

It would help if we knew what the oracle error was.

Bill
Oracle DBA/Developer
New York State, USA

 

[Maven4Champ]

It's a permissions error at the moment (working with a DBA resolve that) because apparently this needs to be created under the sys admin account.

Regardless, if I could gain some knowledge around how to include multiple MVs for refresh within this syntax. As well, I didn't want to send it on to the sys admin without verifying it works first - something I can't do personally without sys admin permissions.

With that said, if it would be possible for someone who may have an MV on their database to create this job and see if it works (after a small modification to the MV name), let me know and I will pass it along to our DBA team.

Thanks!

 

[Turkbear]

Hi,
One of our DBAs has set up a batch file (actually a cmd file) that logs into the instance and then calls the DBMS_MVIEW.REFRESH function multiple times,once for each MV that needs refreshing..This batch file is then run on a schedule using Windows' scheduled tasks manager..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Beilstwh]

Why not setup refresh groups or a call to refresh all mviews, Then you only have to run one job. See the following links

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_mview.htm#sthref4932

http://download.oracle.com/docs/cd/B19306_01/server.102/b14227/rarrefreshpac.htm#REPMA018

Bill
Oracle DBA/Developer
New York State, USA

 

[Maven4Champ]

Turkbear,

This isn't an option because we are on a UNIX server and the DBAs want a more in-house Oracle solution to the issue instead of an external executable that would run. That is how I originally set this up (using a .sql file and .bat file) but to no avail.

 

[Maven4Champ]

We can't refresh all MVIEWS at once because some have dependencies and also due to tablespace, load, etc. thus we want them scheduled.

As for the job I posted, you can refresh more than one MV in that job. I just wasn't 100% sure on the syntax of adding another MV to the exec statement - whether it be with a comma, semicolon, etc.

As for the refresh group, isn't that still something that needs to be kicked off internally? with the DBMS_SCHEDULER it doesn the same thing but on a consistent scheduled basis.

I am really looking for confirmation, if you will, that my script above would work and also the proper syntax to add mutliple MVs to the exec statement.

If I can have that, I think I would be good to go from my perspective and also from that of the DBA Team.

Thanks!

 

[Turkbear]

Hi,
Based on the docs it appears from this:

Multiple materialized views can be refreshed at the same time, and they do not all have to use the same refresh method. To give them different refresh methods, specify multiple method codes in the same order as the list of materialized views (without commas). For example, the following specifies that cal_month_sales_mv be completely refreshed and fweek_pscat_sales_mv receive a fast refresh:

DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV, FWEEK_PSCAT_SALES_MV', 'CF', '',
TRUE, FALSE, 0,0,0, FALSE);

That a list, separated by commas, of the views to refresh is how it is done..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Maven4Champ]

Thanks Turkbear!

It looks as is if all the MVs go in single quotes ONCE and are then separated by a comma as you pointed out.

If that is the case, I will send this on to our DBA to test. I will add up one more MV though so I can test the duplicate MV refresh.

Thanks!

 

[Maven4Champ]

Greetings all, I received permissions to create jobs yesterday and it parsed fine with my syntax. However it was scheduled to refresh 2 MVs in a particular order at midnight and it apparently did not.

If I select * from dba_scheduler_jobs, I see the job there however it is not in my JOBS view from TOAD - curiously enough, when viewing the dba_scheduler_jobs view, it shows that the job did kick off at midnight but didn't refresh anything according to the data I have in the views.

So with that said, I apparently missed a step somewhere. For it to not show up in jobs is curious to me and that it didn't refresh is also odd. Anyone know which log this might get stored in so that I could see its results and also how to delete the job and start over? I assume I have to have some type of drop job permissions?

 

[Maven4Champ]

Well I found what I was looking for. The job did indeed fail. Here is my erorr:

ORA-06550: line 5, column 49:
PLS-00103: Encountered the symbol "MVIEW_1" when expecting one of the following:

   . ( ) , * @ % & = - + < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like
   between || multiset member SUBMULTISET_

Here is my syntax to create the job:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    JOB_NAME        => 'MY_MV_REFRESH',
    JOB_TYPE        => 'PLSQL_BLOCK',
	JOB_ACTION		=> 'EXEC DBMS_MVIEW.REFRESH('MVIEW1','MVIEW2','C')',
    START_DATE      => SYSTIMESTAMP,
    REPEAT_INTERVAL => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    END_DATE        => NULL,
    ENABLED         => TRUE,
    COMMENTS        => 'This job is used for the refreshing of materialized views at 12AM each day.');
END;
/

What I am assuming has occured is, the single quote on line 4 in front of the EXEC statement is getting closed at the beginning of 'MVIEW1' and then MVIEW1 is considered to be "out of place" in the code because its not surround by another single quote.

Is there a way to workaround this as executing this directly in TOAD works like a charm:

EXEC DBMS_MVIEW.REFRESH('MVIEW1','MVIEW2','C')

 

[Maven4Champ]

BTW,

The names of the views were changed from MVIEW_1 to MVIEW1 which is why the above looks like it would be incorrect. Try not to get too hung up on the names as that is just a matter of anonymizing the true names of the MVs.

 

[Maven4Champ]

Ok guys, failed again - this time I took a different approach. I created a procedure that refreshes the MVs and called the procedure within my job. Code now looks like this:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    JOB_NAME        => 'DISCO_MV_REFRESH',
    JOB_TYPE        => 'PLSQL_BLOCK',
	JOB_ACTION		=> 'EXEC DISCO_MVIEWS',
    START_DATE      => SYSTIMESTAMP,
    REPEAT_INTERVAL => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    END_DATE        => NULL,
    ENABLED         => TRUE,
    COMMENTS        => 'This job is used for the refreshing of materialized views at 12AM each day.');
END;
/

The job failed at 12AM this morning with the error:

ORA-06550: line ORA-06550: line 1, column 361:
PLS-00103: Encountered the symbol "DISCO_MVIEWS" when expecting one of the following:

   := . ( @ % ;
The symbol "; was inserted before "DISCO_MVIEWS" to continue.
, column :

What am I missing. Simply running EXEC DISCO_MVIEWS works fine so shouldn't that be the same call I make in my job?

 

[Turkbear]

Hi,
In your original Proc:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    JOB_NAME        => 'MY_MV_REFRESH',
    JOB_TYPE        => 'PLSQL_BLOCK',
[COLOR=red]
    JOB_ACTION        => 'EXEC DBMS_MVIEW.REFRESH('MVIEW1','MVIEW2','C')',
[/color]
    START_DATE      => SYSTIMESTAMP,
    REPEAT_INTERVAL => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    END_DATE        => NULL,
    ENABLED         => TRUE,
    COMMENTS        => 'This job is used for the refreshing of materialized views at 12AM each day.');
END;
/

The red part should be ( if I read the docs right):

  JOB_ACTION        => 'EXEC DBMS_MVIEW.REFRESH('MVIEW1,MVIEW2','C')',

List, comma-separated, within 1 set of quote marks, not around each view name..








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Maven4Champ]

Figured it out...

If I put the following in the JOB_ACTION => then it work:

JOB_ACTION		=> 'BEGIN DISCO_MVIEWS(); END;',