exec dbms_mview.refresh(list=>'my_MV',method=>'F') in

dataservices job design
1

Hi Guys,
Can anyone plese help me with executing the mentioned below script in data Integrator

exec dbms_mview.refresh(list=>‘my_MV’,method=>‘F’)

I tried to Format in all possible ways but still get the error.

Any Help will be appreciated.

Thanks

Tarunvig :us: (BOB member since 2011-01-18)

2

you mean using the sql function?

SQL('DATASTORE_NAME','exec dbms_mview.refresh(list=>\'my_MV\',method=>\'F\')');

I just hope this should work.

ganeshxp :us: (BOB member since 2008-07-17)

3

Thanks Ganesh,

Had to modify the code a Little bit

sql(‘DS_EDW_HR_STG’,‘begin DBMS_MVIEW.REFRESH (list=>‘my_MV’,method=>‘F’); end;’);

This works perfectly Fine.

Thanks for your help.

Tarunvig :us: (BOB member since 2011-01-18)

4

If you want to get a bit fancy you can use the Oracle scheduler to execute the MV refresh. The benefit of this is that you can make the call to the scheduler such that it immediately returns control to the ETL job without waiting for the refresh to complete. This is a fire-and-forget type call.

I wrote a module like this for a client. It worked so well for materialized view refreshes that we started to use it for gathering database statistics.

eganjp :us: (BOB member since 2007-09-12)

5

Hi All,

Replying to this post as I am facing a new Problem now.

Somehow the MV logs are not getting refreshed when I use DI to refresh MV’s.

Dont know what wrong I am doing.

Here is what I am doing.

  1. A DI Job to load data into Target Table. (this is a Simple Field to Field Map with a where clause on Date).
  2. A Script after the DF to Refresh MV’s.

Things Look fine when I execute the JOB.

However when I comment the code to refresh MV’s , somehow the MV log’s which should be populated in case of changes on the base table are not getting refreshed. When I try it manually on the Database, It works fine. But with DI job somehow I am facing this issue.

Please help… :frowning:

Tarunvig :us: (BOB member since 2011-01-18)

6

One more Thing…

If I execute My Job in Debug Mode. Everything wokrs Wonders.
(From Data reflected in MV’s to MV log changes’If SQL to refresh MV is commented’)

However when I execute the Job, MV’s are not refreshed.

Any Suggestions?

Tarunvig :us: (BOB member since 2011-01-18)

7

Configuration issue? The issue with the log isn’t explained so I don’t see how we can help. What log is it? How are entries made to the log?

eganjp :us: (BOB member since 2007-09-12)

8

Hi Jim,

Thanks for replying.

Its a Materialized View log which Is required in order to have a Fast refresh on materialized View.This Log is created on the Base Table.
Syntax :-

CREATE MATERIALIZED VIEW LOG ON edw_hr_stg.test_recovery2 with primary key;

Now any Update/Delete/Insert on the base Table will automatically populate the Log table with Primary key of the Base table and type of operation performed on it.

The whole flow (Job) is pretty Simple. (A Field to Field mapping).

The confusing thing is that when I execute this Job in DEBUG mode, things go fine and I can see my MV being changed Accordingly.

However the Normal execution of the Job causes problems.

FYI :- Syntax to create MV
CREATE MATERIALIZED VIEW EDW_HR_STG.MY_MV
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS SELECT * FROM EDW_HR_STG.TEST_RECOVERY2

Syntax to refresh this MV via DI
:-
sql(‘DS_EDW_HR_STG’,‘begin DBMS_MVIEW.REFRESH (list=>‘my_MV’,method=>‘F’); end;’);

Thanks in advance

Tarunvig :us: (BOB member since 2011-01-18)

9

Well…

Finally Deleting and recreating the Job seems to be Helping me.

But Still wondering, why would a DEBUG work fine and a Normal run won’t refresh the MV> :?

Tarunvig :us: (BOB member since 2011-01-18)

10

Crazy. We all know that DI behaves very very different when we use a Execute Mode to a Debug mode. Right ? :crazy_face:

ganeshxp :us: (BOB member since 2008-07-17)

11

I dunno. Maybe because debug is slower? :crazy_face:

eganjp :us: (BOB member since 2007-09-12)

12

Haha that is a clever answer. :rotf:

But I suspect this issue is raised in forum long time back also. I am not able to dig and find the link for that :oops:

ganeshxp :us: (BOB member since 2008-07-17)

13

Ya even If debugger was Slower and refresh Took time, it almost completed in 15 seconds and the data was reflected.

Same Normal mode gets completed in like 5 seconds and I waited for almost 10 minutes to see if MV is taking time to refresh. (Which obviously was not the case as its wokring fine now).

Anyways, Thanks for you help all.

@Ganesh, in case you find that Post, please let us know about the same.

Thanks Guys!!

Tarunvig :us: (BOB member since 2011-01-18)

14

Alright found one of the post from Werner.

But his example is pretty much same to what you did.

Is that something we cannot re-produce? I mean the issue you faced?

ganeshxp :us: (BOB member since 2008-07-17)

15

I am not sure If I can replicate that issue.
Now I have 2 Jobs which refresh the MV and everything is working Fine.
(Irrespective of refreshing MV from DI or Not).

Next Time If I encounter this Issue, I will save the ATL, That way I hope we may be able to replicate it whenever we want.

Thanks for the Post though.

Tarunvig :us: (BOB member since 2011-01-18)