Advertisement

06.06.2008 at 02:35PM PDT, ID: 23465162
[x]
Attachment Details

Problem creating Oracle materialized view (snapshot).

Asked by stanner in SQL Query Syntax, Oracle Database, PL / SQL

Tags: ,

I am trying to familiarize myself with materialized views. I want to use them for a data warehousing scenario, so only really need  them to refresh once a day. However, I have been experimenting with a small amount of data to see how it all works.

The problem I'm having is that if I use the "REFRESH FORCE" clause, it creates the view and transfers the data once... and only once. Even if I specify START WITH and NEXT, it never refreshes again on it's own. However, if I specify REFRESH FAST, I get the error message "ORA-00942: table or view does not exist".

As I said, what I really want in the end is a materialized view that will refresh itself (without me telling it to) once a day, based on either the primary key or the rowid. Any direction would be helpful here, as I'm not quite sure how the REFRESH FAST/FORCE ON DEMAND/COMMIT work in conjunction with the START WITH and NEXT options. I assumed that REFRESH FAST ON COMMIT means that it would constantly update itself as data is committed to the source table, which is not really what I want. However, with FORCE, it seems like you have to manually force it to update. Is there a way to get the "schedule" to work?

Thanks for any advice.Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:

CREATE MATERIALIZED VIEW mv_pcard_transaction
     REFRESH FAST
    START WITH SYSDATE
  NEXT  SYSDATE + 1/96
  WITH PRIMARY KEY
AS
select pc.transaction_id, ... <other stuff>
from pcard.pcard_transaction@common pc
where bla bla bal
[+][-]06.06.2008 at 11:52PM PDT, ID: 21734487

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.06.2008 at 11:58PM PDT, ID: 21734503

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.07.2008 at 12:00AM PDT, ID: 21734505

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.07.2008 at 03:09PM PDT, ID: 21736924

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.08.2008 at 01:18AM PDT, ID: 21737871

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.08.2008 at 01:28AM PDT, ID: 21737887

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: SQL Query Syntax, Oracle Database, PL / SQL
Tags: Oracle, ORA-00942: table or view does not exist
Sign Up Now!
Solution Provided By: rolutola
Participating Experts: 1
Solution Grade: A
 
 
[+][-]06.08.2008 at 12:00PM PDT, ID: 21739221

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.09.2008 at 12:06PM PDT, ID: 21745704

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.10.2008 at 03:31PM PDT, ID: 21755640

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628