Sheduling Materialzed View (Oracle 8i)

Please post your CREATE MATERIALIZED VIEW command.

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;

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

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

OK, I remember that USER_MVIEWS is not trustable on 8i. Check DBA_SNAPSHOTS instead.
You can refer to another question https://www.experts-exchange.com/questions/21059666/snapshot-replication.html

Is your job_queue_process parameter > 0 otherwise the scheduled job will never run.

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

>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;

Last question...

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

It is a date convertion problem ?

Thanks

???
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 ?

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