Ora Error 12008
Contents |
UTC Answered by: Tom Kyte � Last updated: September 26, 2012 - 12:07 pm UTC Category: Database � Version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 ora-12008: error in materialized view refresh path ora-00942: table or view does not exist Whilst you are here, check out some content from the AskTom team: kkzifr3g: encountered error ora-12008 Automation, Cleanup, and Database Maintenance Latest Followup You Asked Hi Tom, I 'm using Oracle Database 10g
Ora-12008 Ora-01555
Enterprise Edition Release 10.2.0.1.0; I got these errors when I tried to refresh my materialized view ; ORA-12008: error in materialized view refresh path ORA-01555: snapshot too old: rollback
Oracle Bug Id 13910043
segment number 5 with name "_SYSSMU5$" too small ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430 I don't understand what mean these errors and why i got them? Someone can help me please ? Thanks regards, and we said... you have set your undo retention period too short - this is ora-12008 error in materialized view refresh path ora-01031 insufficient privileges a classic ora-1555 (search asktom for that, hundreds of articles on ora-1555) increase your undo retention to be longer than your longest running query (or refresh in this case) make sure you have sufficient undo tablespace configured as well. Reviews Write a Review I´m same problem October 16, 2009 - 9:25 am UTC Reviewer: Bin from Brazil I have same problem about refresh materialized view. My materialized view has been executing in a job in diferents times, at 6:00 AM and at 4:00Pm but it error happening only 6:00AM would be need increase the retention period too? snapshot too old error why on materialized view refresh September 20, 2012 - 12:34 pm UTC Reviewer: raju from india Hi Tom, first of all, thanks for your great posts on different topics of Oracle. 1.i understand that this error happens on the long running queries(while the accessed rollback segments are overwritten). But why this error occurs during materialized view refresh(long running)? 2.i asked the above question, because, i have set the undo_retention to 23000
with left outer join: CREATE MATERIALIZED VIEW table_mw REFRESH FAST ON COMMIT WITH ROWID AS SELECT
Ora-12008 Ora-00942
v.ROWID v_rid, t.ROWID t_rid, s.ROWID s_rid, ora-12008 ora-00913 v.*, s.dfr, s.dfr FROM table_v v, ora-12008 error in materialized view refresh path ora-01555 table_t t, table_s s WHERE v.ref_id = t.id (+) AND v.ref_id = s.id (+) / CREATE MATERIALIZED VIEW LOG ON table_v WITH ROWID / CREATE MATERIALIZED VIEW https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:409663900346414175 LOG ON table_t WITH ROWID / CREATE MATERIALIZED VIEW LOG ON table_s WITH ROWID / Now, let's keep aside that not only you have to comply with documented rules for view to be fast refreshable (http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#i1006674), there is also one undocumented restriction: ANSI-joins are not supported. With that solved, we did a simple UPDATE of one row in table TABLE_V. Everything looked fine, until we wanted http://michalsimonik.blogspot.com/2015/06/ora-12008-error-in-materialized-view.html to commit. Commit returned following error stack: ORA-12008: error in materialized view refresh path ORA-00942: table or view does not exist Cause: Table SNAP$_
Library http://psoug.org/oraerror/ORA-12008.htm mySQL Code Library PHP Code Library JavaScript Code Library Oracle Terms & Definitions Oracle Error Codes PSOUG Community Blogs Oracle http://www.orafaq.com/forum/t/178672/ Jobs Board PSOUG Forum Oracle User Group Directory Free Oracle Magazines Online Learning Center PSOUG Presentations Advanced Code Search News and error in Events Sponsors Page Submit Code Contact Us Oracle Error: ORA-12008 Error Description: Error in materialized view refresh path Error Cause: Table SNAP$_
(merged) [message #542443] Tue, 07 February 2012 05:54 mvmkandan Messages: 68Registered: May 2010 Location: Trivendrum Member I have created a Table with one column in user 'Test' Database 'Test'. Then i have created a Materialized view based on the Table in 'TestMV' in same database 'Test'. Its working fine. But after adding one column in the table, if i go to refresh the Materialized view the following Error has occured. * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-00913: too many values ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431 ORA-06512: at line 2 ** Kindly Give your views to resolve this issue. Thanks in Advance Veera Report message to a moderator Re: Materialized View Refresh [message #542446 is a reply to message #542443] Tue, 07 February 2012 06:00 Michel Cadot Messages: 63911Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator Recreate the MVIEW. Regards Michel Report message to a moderator Re: Materialized View Refresh [message #542447 is a reply to message #542443] Tue, 07 February 2012 06:01 Littlefoot Messages: 20850Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator I bet you created a materialized view using a '*':CREATE MATERIALIZED VIEW mv_whatever AS SELECT * FROM ... You should have put some more effort and actually typed column list in there:SELECT col1, col2, ...If you did that, you wouldn't have that problem. What to do? Recreate the materialized view so that it reflects a new table outlook. Report message to a moderator Re: Materialized View Refresh [message #542449 is a reply to message #542443] Tue, 07 February 2012 06:05 arup_kc Messages: 9Registered: January 2012 Location: India Junior Member better you re create the materialized view once again and then test. Report message to a moderator Re: Materialized View Refresh [message #542452 is a reply to message #542449] Tue, 07 February 2012 06:27 mvmkandan Messages: 68Registered: May 2010 Location: Trivendrum Member Hi LittleFoot, I got it. Thanks Veera Report message to a moderator Refresh View [message #542453 is a reply to message #542443] Tue, 07 February 2012 06:29 mvmkandan Messages: 68Registered: May 2010 Location: Trivendrum Member Hi, I have created a table with 2 columns and i have created the view as follows 'Create view testview as select * from testtable' view is created successfully. After adding one column in the testtable, the view