?
Solved

Sheduling Materialzed View (Oracle 8i)

Posted on 2004-09-06
12
Medium Priority
?
1,174 Views
Last Modified: 2008-01-09
Hi,

I created thre Materialized view and I want to auto recreate it every two hours....

Here is my sql cmd :

Alter materialized view mv_artikel_desc
refresh complete
start with sysdate
next sysdate + 2/ 24;

After that The compile flag of this materialized view is Needs_compile...

It seems that it doesn't recreate the view every two hours....

Thanks

David
0
Comment
Question by:dcampillo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 11988378
Please post your CREATE MATERIALIZED VIEW command.
0
 

Author Comment

by:dcampillo
ID: 11988405
Here is my cmd :

I use the "on demand" at creation time because, the Schema Manager, pop up an error about dates. And after creation I altered the MV with this to schedule the recreation :

CREATE SNAPSHOT  "LAKOS"."MV_ARTIKEL_TEST"
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
select Bezeichnung from lakos.artikel group by bezeichnung

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(
     ownname => 'LAKOS',
     tabname => 'MV_ARTIKEL_TEST');
END;


Alter materialized view mv_artikel_desc
refresh complete
start with sysdate
next sysdate + 2/ 24;


0
 

Author Comment

by:dcampillo
ID: 11988427
Here is the DDL output :

CREATE SNAPSHOT  "LAKOS"."MV_ARTIKEL_DESC"
PCTFREE 10 PCTUSED 40 MAXTRANS 255 STORAGE ( INITIAL 32K NEXT 64K MINEXTENTS 1 MAXEXTENTS 100 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1)
TABLESPACE "USER_INX"
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH to_date('06-Sep-2004 01:37:54 PM','dd-Mon-yyyy HH:MI:SS AM')
NEXT sysdate  + 2/24
AS
select Bezeichnung from lakos.artikel group by bezeichnung

----

The schedule seems ok but, the MV status not ... Look :

MVIEW_NAME                     STALENESS COMPILE_STATE
------------------------------ --------- -------------
MV_ARTIKEL_DESC                FRESH     NEEDS_COMPILE
MV_ARTIKEL_SIZE                FRESH     VALID
MV_ARTIKEL_STOCK               FRESH     VALID

David
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 8

Accepted Solution

by:
Pierrick LOUBIER earned 500 total points
ID: 11988429
Try this :

CREATE MATERIALIZED VIEW "LAKOS"."MV_ARTIKEL_TEST"
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('06-09-2004 12:00:00','dd-mm-yyyy hh24:mi:ss')
NEXT TRUNC(SYSDATE)+2/24  
AS
select Bezeichnung from lakos.artikel group by bezeichnung;
0
 

Author Comment

by:dcampillo
ID: 11988468
With the Trunc fn on sysdate I get those errors ! But without Trunc everything, the mv was created... Waiting for check is the schedule is working

ERROR at line 7:
ORA-23420: interval must evaluate to a time in the future
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 129
ORA-06512: at "SYS.DBMS_IREFRESH", line 92
ORA-06512: at "SYS.DBMS_IREFRESH", line 275
ORA-06512: at "SYS.DBMS_REFRESH", line 109
ORA-06512: at "SYS.DBMS_REFRESH", line 83
ORA-06512: at "SYS.DBMS_ISNAPSHOT", line 73
ORA-06512: at line 1
0
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 11989107
OK, I remember that USER_MVIEWS is not trustable on 8i. Check DBA_SNAPSHOTS instead.
You can refer to another question http://www.experts-exchange.com/Databases/Oracle/Q_21059666.html
0
 
LVL 7

Expert Comment

by:BobMc
ID: 11990368
Is your job_queue_process parameter > 0 otherwise the scheduled job will never run.
0
 

Author Comment

by:dcampillo
ID: 11994624
Hi,

I have changed the parameter job_queue_process to 1 from the OEM. But I am not sure if oracle has taken the change.

how to check if the materialized view are correctly refreshed (compiled) every two hour ?

Thanks
0
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 11994713
>But I am not sure if oracle has taken the change.
To check that parameter was really updated, open an SQL*Plus prompt, connect as system, then enter :
SHOW PARAMETER job_queue_processes;
If not :
ALTER SYSTEM SET job_queue_processes=1;

>how to check if the materialized view are correctly refreshed (compiled) every two hour ?
SELECT name, last_refresh, status
FROM dba_snapshots;
0
 

Author Comment

by:dcampillo
ID: 11994731
Last question...

The last_refresh col = 01-JAN-50 ??? What does it mean ?

It is a date convertion problem ?

Thanks
0
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 11994794
???
Try to refresh it manually :

BEGIN
  DBMS_SNAPSHOT.REFRESH('LAKOS.MV_ARTIKEL_TEST');
END;
/

A little precision : in you previous posts, you mention MV_ARTIKEL_TEST and MV_ARTIKEL_DESC. Isn't there any confusion ?
0
 

Author Comment

by:dcampillo
ID: 11994833
HI,

I can refresh them but look at the output :

NAME                           LAST_REFR STATUS
------------------------------ --------- ------
MV_ARTIKEL_SIZE                01-JAN-50 VALID
MV_ARTIKEL_DESC                01-JAN-50 VALID
MV_ARTIKEL_STOCK               01-JAN-50 VALID

Thanks for your help, i will look why the date isn't correct...

Thanks a lot...

David
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question