Solved

Sheduling Materialzed View (Oracle 8i)

Posted on 2004-09-06
12
1,054 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
 
LVL 8

Accepted Solution

by:
Pierrick LOUBIER earned 125 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now