Home » Server Options » Replication » materialized view on demand refresh problem-URGENT
materialized view on demand refresh problem-URGENT [message #75204] Thu, 04 July 2002 04:04 Go to next message
Messages: 12
Registered: June 2001
Junior Member
Hi guys!
I am new to materialized views and have created a materialized view on a on a table.
The view is scheduled to refresh every 1 hour and is ON DEMAND.
I am getting following error while executing the procedure 'refresh' from built-in dbms_mview oracle package.
spatialstats_daily_5 is name of the materialized view already successfully created.
SQL> execute dbms_mview.refresh('spatialstats_daily_5','C');
BEGIN dbms_mview.refresh('spatialstats_daily_5','C'); END;

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.DBMS_DEFER_SYS" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 814
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 872
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 852
ORA-06512: at line 1
Oracle server is 9.0.1.
I dont get this error on oracle8 and everythings fine there.
Is my understanding right that executing the 'refresh' procedure triggers the consitent and automatic refresh of MV and is necessary?
From ORA error ACTION i understand that 'refresh' procedure is refrencing some non-existent procedure/package.
Is some configuration in init.ora missing? i have already added "job_queue_processes = 2".
One more problem is that if i set "job_queue_interval", and bounce the DB, it says that this parameter has become obsolete !!
Any inputs will be highly appreciated.
Re: materialized view on demand refresh problem-URGENT [message #75520 is a reply to message #75204] Tue, 13 July 2004 03:58 Go to previous message
Messages: 6
Registered: July 2004
Junior Member
i get the same problem with oracle8, i need a trigger to do this automaticaly...

my trigger
create or replace trigger t1_update_mw_trig
after update on T1 (c11, c12)
for each row
dbms_mview.refresh(para1, para2, para3...);

my errors
ORA-04092: isnīt possible COMMIT in trigger
ORA-06512: em "SYS.DBMS_SNAPSHOT", line 869
ORA-06512: em "SYS.DBMS_SNAPSHOT", line 852
ORA-06512: em "ANIID.T1_UPDATE_MW_TRIG", line 2
ORA-04088: error during the execute trigger 'ANIID.T1_UPDATE_MW_TRIG'

and a fill months ago this trigger function perfectly...
Previous Topic: Replication - Minimum Required Spec.
Next Topic: materialized view refresh problem - URGENT
Goto Forum:

Current Time: Mon Dec 06 09:28:24 CST 2021