Home » SQL & PL/SQL » SQL & PL/SQL » SNAPSHOT NOT REFLECTED AUTOMATICALLY
SNAPSHOT NOT REFLECTED AUTOMATICALLY [message #221802] Wed, 28 February 2007 09:06 Go to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
Hi,
I have created a snapshot with BUILD IMMEDIATE option but this is not getting reflected on change.

However this is reflected whenever I manually do DBMS_REFRESH.REFRESH.

Thanks in Advance.
Re: SNAPSHOT NOT REFLECTED AUTOMATICALLY [message #221810 is a reply to message #221802] Wed, 28 February 2007 09:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need a Snapshot that is valid for REFRESH ON COMMIT in order to get it to automatically refresh when changes are made to the underlying tables.
Re: SNAPSHOT NOT REFLECTED AUTOMATICALLY [message #221902 is a reply to message #221810] Wed, 28 February 2007 23:33 Go to previous messageGo to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
Hi,
It seems the MVIEW is ok however job_queue_processes was zero which should be >=1.

Thanks for your help.
Re: SNAPSHOT NOT REFLECTED AUTOMATICALLY [message #221938 is a reply to message #221902] Thu, 01 March 2007 03:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Unless you a re submitting DBMS_JOBSs to rebuild your MViews, the problem is unlikely to be the setting of the Job_Queue_Processes parameter.
Re: SNAPSHOT NOT REFLECTED AUTOMATICALLY [message #221943 is a reply to message #221938] Thu, 01 March 2007 03:37 Go to previous messageGo to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
Hi,
Can you please explain what do you mean by rebuilding dbms_jobs. I can see an entry in dbms_jobs however last date is null.

Thanks in Advance.
Re: SNAPSHOT NOT REFLECTED AUTOMATICALLY [message #221945 is a reply to message #221943] Thu, 01 March 2007 03:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't really know how to make what I said much clearer - I'm not talking about rebuilding DBMS_JOBS at all.

The Oracle Parameter 'Job_Queue_Processes' only controls the number of dedicated processes for running jobs defined by DBMS_JOBS. It has no effect on the operation or refresh of materialized views.

The only way I can think that this parameter might be affecting your ability to refresh your Mviews is if you are submitting a Dbms_Job to do the refresh for you.
Re: SNAPSHOT NOT REFLECTED AUTOMATICALLY [message #221946 is a reply to message #221945] Thu, 01 March 2007 03:46 Go to previous message
ganeshsv
Messages: 51
Registered: January 2006
Member
Hi, Sorry. It was a typo error. Thanks for your explanation.


Thanks,
YesGee.
Previous Topic: Store and Manipulate pdf in Oracle 9i
Next Topic: insert multiple rows
Goto Forum:
  


Current Time: Sun Dec 11 02:32:01 CST 2016

Total time taken to generate the page: 0.08826 seconds