Home » SQL & PL/SQL » SQL & PL/SQL » Snapshot refresh fails with ORA-12008, ORA-01410
Snapshot refresh fails with ORA-12008, ORA-01410 [message #194352] Thu, 21 September 2006 11:34 Go to next message
jfertitta
Messages: 3
Registered: September 2006
Location: Dallas, Texas
Junior Member
I'm running hourly snapshot refreshes froom a shell scrupt kicked off from cron, and I receive the following ORA errors intermittantly:

ORA-12008: error in materialized view refresh path
ORA-01410: invalid ROWID
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 8

I'm not sure why sometimes it fails and sometimes it completes without errors. Has anyone encountered this before that can suggest a solution?

I couldn't find anything related after searching existing messages so I thought I would post.

Thanks in advance.
Re: Snapshot refresh fails with ORA-12008, ORA-01410 [message #194388 is a reply to message #194352] Thu, 21 September 2006 20:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You get an ORA-01410 when you select from a table using a rowid that doesn't exist. eg.
SELECT *
FROM DUAL
WHERE rowid = '0000000000000000';

This is a little counter-intuitive; you would expect No Rows Returned instead.

When you do a MV Refresh, Oracle converts that request into a complex series of SQL statements that it performs in the background. If your MV is REFRESH FAST USING ROWID, or is a join-MV, then it is quite possible - nay likely - that one of the SQLs being performed in the background contains a
WHERE ROWID =
predicate.

Now, I'll assume that it is a REFRESH FAST MV, because it is darned unlikely to happen with a complete refresh. Correct me if I'm wrong.

Here's what I'd do:

  1. Try to change it to REFRESH FAST USING PRIMARY KEY rather than USING ROWID. If it is a join-MV without aggregates, you may not succeed. I can find nothing in the documentation about this, but I have not successfully created a non-aggregate-join-MV without including ROWID in the MV-logs. If the MV is refreshed using the PK, there will be no need to use ROWID's. This is not so much fixing the problem, as avoiding it.
  2. Make sure ALL of your MV Logs include WITH SEQUENCE. If you don't do this, then Oracle will not necessarily apply all of the INSERTs, UPDATEs, and DELETEs in the correct order - it could attempt to apply an UPDATE and a DELETE in the wrong order, and get the ROWID error when the UPDATE does not find the row. This is not an exact science - I used to have a lot of trouble with MVs until I included WITH SEQUENCE on all of the logs. Since then, no errors at all.
  3. If all else fails, use SQL*Trace to trace every refresh until the error re-occurs. When it does, run it through TK*Prof to see what SQLs are being applied. This will at least give you a clue as to what is happening - you should be able to step through the SQLs shown in TK*Prof and see how the error happened (I've done this). What you will probably find is that Oracle has applied the changes to dependent tables in the wrong order. This will either be your fault (eg. not including WITH SEQUENCE) or you will have found a genuine error in Oracle (raise an SR with Oracle).



Ross Leishman
Re: Snapshot refresh fails with ORA-12008, ORA-01410 [message #194547 is a reply to message #194388] Fri, 22 September 2006 10:41 Go to previous messageGo to next message
jfertitta
Messages: 3
Registered: September 2006
Location: Dallas, Texas
Junior Member
Ross,

In answer to your question, this MV is refresh on demand using primary key, so unfortunately I cannot apply the first solution you suggest. The second one, however, is possible as the MV log does not include WITH SEQUENCE.

I'm going to try this and monitor the results.

Thanks for your response.

Regards,

Joe Fertitta
Re: Snapshot refresh fails with ORA-12008, ORA-01410 [message #194607 is a reply to message #194547] Fri, 22 September 2006 23:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
jfertitta wrote on Sat, 23 September 2006 01:41

In answer to your question, this MV is refresh on demand using primary key...


So, it is WITH PRIMARY KEY, but is still using ROWID for something. Just for my own interest, is it a JOIN MV (ie. join of two or more tables without aggregation).

Ross Leishman
Re: Snapshot refresh fails with ORA-12008, ORA-01410 [message #194825 is a reply to message #194607] Mon, 25 September 2006 09:40 Go to previous messageGo to next message
jfertitta
Messages: 3
Registered: September 2006
Location: Dallas, Texas
Junior Member
This MV is not using a JOIN. The MV is created using a simple

SELECT <column_names>
FROM <table_name>

That's one reason I've not been able to identify the problem. Nothing at all to indicate why this error may randomly occcur.

After discussing it we are going to re-create the snapshot log using WITH SEQUENCE. Thanks for your input on this.
Re: Snapshot refresh fails with ORA-12008, ORA-01410 [message #268782 is a reply to message #194352] Wed, 19 September 2007 12:47 Go to previous messageGo to next message
ajc1970
Messages: 1
Registered: September 2007
Junior Member
jfertitta wrote on Thu, 21 September 2006 11:34
I'm running hourly snapshot refreshes froom a shell scrupt kicked off from cron, and I receive the following ORA errors intermittantly:

ORA-12008: error in materialized view refresh path
ORA-01410: invalid ROWID
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 8

I'm not sure why sometimes it fails and sometimes it completes without errors. Has anyone encountered this before that can suggest a solution?

I couldn't find anything related after searching existing messages so I thought I would post.

Thanks in advance.


is ROW_MOVEMENT enabled on the MASTER table?
Re: Snapshot refresh fails with ORA-12008, ORA-01410 [message #268852 is a reply to message #268782] Wed, 19 September 2007 22:38 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I doubt @jfertitta is still trying to solve this after a year, so don't hold your breath waiting to see if your advice was helpful.
Previous Topic: Query Performance Across Date Ranges
Next Topic: update
Goto Forum:
  


Current Time: Sat Dec 10 01:16:15 CST 2016

Total time taken to generate the page: 0.11550 seconds