OraFAQ Forum: Replication » materialized View Error : existing state of packages has been discarded

Home » Server Options » Replication » materialized View Error : existing state of packages has been discarded

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

materialized View Error : existing state of packages has been discarded [message #64449]

Mon, 31 January 2005 00:55

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

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Seems a dependent object of "mtourprogram" is been altered.
Check all the dependencies.

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
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?

Report message to a moderator

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

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.

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

ORA-04068: Existing state of packages has been discarded [message #298261 is a reply to message #64477]

Tue, 05 February 2008 08:29

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

Report message to a moderator

Re: ORA-04068: Existing state of packages has been discarded [message #298263 is a reply to message #298261]

Tue, 05 February 2008 08:34

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: ORA-04068: Existing state of packages has been discarded [message #298270 is a reply to message #298261]

Tue, 05 February 2008 08:57

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 19 01:13:12 CDT 2025