• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1301
  • Last Modified:

Sheduling Materialzed View (Oracle 8i)

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
dcampillo
Asked:
dcampillo
  • 6
  • 5
1 Solution
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
Please post your CREATE MATERIALIZED VIEW command.
0
 
dcampilloAuthor Commented:
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
 
dcampilloAuthor Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
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
 
dcampilloAuthor Commented:
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
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
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
 
BobMcCommented:
Is your job_queue_process parameter > 0 otherwise the scheduled job will never run.
0
 
dcampilloAuthor Commented:
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
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
>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
 
dcampilloAuthor Commented:
Last question...

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

It is a date convertion problem ?

Thanks
0
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
???
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
 
dcampilloAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now