Home » SQL & PL/SQL » SQL & PL/SQL » Materialised Views
Materialised Views [message #228363] Mon, 02 April 2007 12:51 Go to next message
143118
Messages: 1
Registered: January 2007
Location: United States
Junior Member
All,

Could anyone help me in this issue.

I have created one materialised view in development instance and when i tried to refresh the Materialised View am getting the following error..

I have double checked that the Mview exists in the instance.

Errors in file /home/oracle/admin/e59dv1/bdump/e59dv1_j002_4499.trc:
ORA-12012: error on auto execute of job 361265
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


Let me know if any thing needed from my side.
Thanks,
Siva...
Re: Materialised Views [message #228432 is a reply to message #228363] Tue, 03 April 2007 00:59 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Can you drop it and recreate it without any errors ?

Also have a look here

[Updated on: Tue, 03 April 2007 01:05]

Report message to a moderator

Re: Materialised Views [message #242595 is a reply to message #228432] Mon, 04 June 2007 04:54 Go to previous messageGo to next message
swasti
Messages: 1
Registered: June 2007
Junior Member
Hi,
I am getting a similar error..

created a materialized view like:

CREATE MATERIALIZED VIEW COMMISSION_SCHEMES_FLAT
REFRESH COMPLETE ON DEMAND
WITH ROWID
START WITH SYSDATE
NEXT SYSDATE + 1
AS
SELECT......

The Select Statement is complex in the sense that it contains distinct and a number of joins involving many tables.

The mat view created is not getting refreshing automatically everyday as it should be. Moreover once it is complied manually, it is becoming an invalid object and on trying to refresh it as :

exec dbms_refresh.refresh('"GECWMIS"."COMMISSION_SCHEMES_FLAT"');

it is throwing the following error:
ERROR at line 1:

ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195

where as the MV does exist..

Please can anyone tell me why this is happening? Is WITH ROWID creating a problem as the select query consists of distinct and joins?

Any help will be appreciated.
Many Thanks
Swasti
Re: Materialised Views [message #243900 is a reply to message #242595] Sun, 10 June 2007 22:44 Go to previous message
lathadba
Messages: 1
Registered: June 2007
Junior Member
Always, the error that is shown before 'ORA-06512' error, will give you a clue. In this case, ORA-00942 should give you a clue.

Moreover, there are some restrictions in using WITH ROWID. We cannot use subqueries, joins and GROUP BY/CONNECT BY clause. It has to be a simple SELECT statement.

Regards,
Latha.
Previous Topic: ORA-00604 problem
Next Topic: Optimize the query
Goto Forum:
  


Current Time: Sun Dec 04 12:36:58 CST 2016

Total time taken to generate the page: 0.11649 seconds