Home » Server Options » Replication » materialized View Error : existing state of packages has been discarded
materialized View Error : existing state of packages has been discarded [message #64449] Mon, 31 January 2005 00:55 Go to next message
Sreedhar Reddy
Messages: 55
Registered: January 2002
Member
On running the below statement the error occurs..please suggest better solution

SQL> exec dbms_mview.refresh('mtourprogram');

BEGIN dbms_mview.refresh('mtourprogram'); 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 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1
Re: materialized View Error : existing state of packages has been discarded [message #64457 is a reply to message #64449] Mon, 31 January 2005 02:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Seems a dependent object of "mtourprogram" is been altered.
Check all the dependencies.
Re: materialized View Error : existing state of packages has been discarded [message #64458 is a reply to message #64457] Mon, 31 January 2005 02:39 Go to previous messageGo to next message
Sreedhar Reddy
Messages: 55
Registered: January 2002
Member
its not with only one materialized view, but with all the materialized views, it shows the same error message
Re: materialized View Error : existing state of packages has been discarded [message #64463 is a reply to message #64449] Mon, 31 January 2005 04:55 Go to previous messageGo to next message
VJ
Messages: 24
Registered: June 2002
Junior Member
what version of oracle are u running .. ?
is the select statement of mview is inturn having any views that reads from package contents .. ?
did u tried compiling all the packages before refreshing mview.. ? Also check if your packages are having any "execute immediates / DDL statements " in those ...thats a known issue if that is the case ...

-V
Re: materialized View Error : existing state of packages has been discarded [message #64466 is a reply to message #64458] Mon, 31 January 2005 05:33 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
First check whether proper execute permissions were given.
try to compile the package SYS.DBMS_DEFER_SYS.
the look up for errors.
It should show the possible reason.

Was there an recent FULL import into this database from a different version of Oracle?.
Was the import done as SYS user?
Re: materialized View Error : existing state of packages has been discarded [message #64472 is a reply to message #64466] Mon, 31 January 2005 22:05 Go to previous messageGo to next message
Sreedhar Reddy
Messages: 55
Registered: January 2002
Member
We are using Oracle 9i Version, no FULL import was taken place.

Currently exec DBMS_MVIEW.REFRESH_ALL is working fine, but refreshing particular materialized view is not taking place.

the particular user has got DBA privileges.
Re: materialized View Error : existing state of packages has been discarded [message #64477 is a reply to message #64472] Tue, 01 February 2005 03:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
These thingies work with invoker rights.
Any privs given through roles may be overridden ( very common in pl/sql). In sql*plus it is a rare case.
try to give the privs explicitly like

sql> grant ALTER ANY SNAPSHOT to userName;

Did try to compile/recreate the snapshot and the DBMS package?
Please try it...it may give more information.
ORA-04068: Existing state of packages has been discarded [message #298261 is a reply to message #64477] Tue, 05 February 2008 08:29 Go to previous messageGo to next message
jyothsna1217
Messages: 9
Registered: February 2008
Location: hyderabad
Junior Member

Hi friends

I tried to execute a pl/sql procedure using dbms_pipe package.
But it's compiling,While executing the error is coming..Can u help me/.....
EXAMPLE IS LIKE THIS;

SQL> CREATE OR REPLACE PROCEDURE sendmessage(msg VARCHAR2) AS
2 status_id NUMBER;
3 BEGIN
4 DBMS_PIPE.PACK_MESSAGE(LENGTH(msg));
5 DBMS_PIPE.PACK_MESSAGE(msg);
6 status_id := DBMS_PIPE.SEND_MESSAGE('send_message');
7 IF status_id != 0
8 THEN
9 raise_application_error(-20099, 'send error');
10 END IF;
11 END sendmessage;
12 /

Procedure created.

SQL> exec sendmessage('hello');
BEGIN sendmessage('hello'); END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "SCOTT.DBMS_PIPE" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SCOTT.SENDMESSAGE", line 4
ORA-06512: at line 1
Re: ORA-04068: Existing state of packages has been discarded [message #298263 is a reply to message #298261] Tue, 05 February 2008 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is not "could not find program unit being called" clear?
I doubt there is a package scott.dbms_pipe.

Next time, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).


Regards
Michel
Re: ORA-04068: Existing state of packages has been discarded [message #298270 is a reply to message #298261] Tue, 05 February 2008 08:57 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You started a new topic on this subject: http://www.orafaq.com/forum/m/298265/102589/#msg_298265
Please use it now and no more use this one.

Regards
Michel
Previous Topic: Index on a snapshot in 8i
Next Topic: create materialized view + insufficient privileges
Goto Forum:
  


Current Time: Thu Mar 28 21:13:51 CDT 2024