Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sheduling Materialzed View (Oracle 8i)

Posted on 2004-09-06
12
Medium Priority
?
1,254 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
What we learned in Webroot's webinar on multi-vector protection.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

564 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