Home » Server Options » Replication » refresh mv using oracle jobs everyday except for sunday @ 6 AM (10.2.0.4.0)
refresh mv using oracle jobs everyday except for sunday @ 6 AM [message #443655] Tue, 16 February 2010 15:46 Go to next message
nishach33
Messages: 3
Registered: February 2010
Location: germantown
Junior Member
i need to refresh mv group everday except for sunday @6 AM using oracle jobs. The MV group gets refreshed everyday now. Can somebody help me with the syntax.

[Updated on: Tue, 16 February 2010 15:48]

Report message to a moderator

Re: refresh mv using oracle jobs everyday except for sunday @ 6 AM [message #443677 is a reply to message #443655] Tue, 16 February 2010 23:07 Go to previous messageGo to next message
Michel Cadot
Messages: 58621
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read DBMS_JOB and follow "DBMS_JOB complex scheduling" link.

Regards
Michel
Re: refresh mv using oracle jobs everyday except for sunday @ 6 AM [message #444056 is a reply to message #443677] Thu, 18 February 2010 09:41 Go to previous messageGo to next message
nishach33
Messages: 3
Registered: February 2010
Location: germantown
Junior Member
declare
my_job number;
begin
dbms_job.submit(job => my_job,
what => 'exec dbms_mview.refresh(<'owner'>.<'object_name'>,'c');',
next_date => to_date('02/1/2010 02:00','MM/DD/YYYY HH24:MI'),
interval => 'trunc(decode(to_char(sysdate+1,''DY''),''SUN'',next_day(sysdate,''MON''),sysdate+1))+2/24');
commit;
end;
/


Can somebody please tell me what is wrong here?
Re: refresh mv using oracle jobs everyday except for sunday @ 6 AM [message #444057 is a reply to message #444056] Thu, 18 February 2010 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 58621
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does make you think something is wrong?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: refresh mv using oracle jobs everyday except for sunday @ 6 AM [message #444062 is a reply to message #443655] Thu, 18 February 2010 10:38 Go to previous messageGo to next message
nishach33
Messages: 3
Registered: February 2010
Location: germantown
Junior Member
Sorry about that. I am getting the below error while trying to run the below syntax. I am pretty new to oracle so I am not able to figure it out.

declare
my_job number;
begin
dbms_job.submit(job => my_job,
what => 'exec dbms_mview.refresh(<'owner'>.<'object_name'>,'c');',
next_date => to_date('02/1/2010 02:00','MM/DD/YYYY HH24:MI'),
interval => 'trunc(decode(to_char(sysdate+1,''DY''),''SUN'',next_day(sysdate,''MON''),sysdate+1))

+2/24');
commit;
end;
/

ERROR at line 5:
ORA-06550: line 5, column 41:
PLS-00103: Encountered the symbol "<
OWNER>" when expecting one of the following:
) , * & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_

[Updated on: Thu, 18 February 2010 10:40]

Report message to a moderator

Re: refresh mv using oracle jobs everyday except for sunday @ 6 AM [message #444067 is a reply to message #444062] Thu, 18 February 2010 10:54 Go to previous message
Michel Cadot
Messages: 58621
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you please follow the link and post accordingly.

For instance, do you see the difference between '' and "?

Regards
Michel
Previous Topic: One way replication from MS sql server to Oracle 10g
Next Topic: Creative conflict resolution
Goto Forum:
  


Current Time: Wed Jul 30 13:46:25 CDT 2014

Total time taken to generate the page: 0.09510 seconds