Ora-12008 Error In Materialized View Refresh Path Ora-01031
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings ora-12008: error in materialized view refresh path ora-00942: table or view does not exist and policies of this site About Us Learn more about Stack Overflow
Ora-12008 Ora-00942
the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation ora-12008 ora-00913 Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it ora-12008 ora-01555 only takes a minute: Sign up Error in materialized view refresh path / permission up vote 2 down vote favorite I have a materalized view I've been using for quite awhile now where I perform a fast refresh every night prior to another job. The PROC gets called through an automated system (Active Batch), and contains the following refresh: DBMS_MVIEW.REFRESH(LIST=>'GLMV_TAX_CODE_HIST',PARALLELISM
Ora-06512: At Line 2563
=> 8); A few weeks ago, I randomly began getting this error: Exit Code Description: ORA-12008: error in materialized view refresh path ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748 After a bit of research, figured it was simply a matter of granting permissions based on my findings, so ran the following, which appeared to work: GRANT SELECT ON MLOG$_TAX_CODE_HIST TO MYUSER; GRANT CREATE ANY materialized view to MYUSER; GRANT ALTER ANY MATERIALIZED VIEW TO MYUSER; Grant ALTER ANY SNAPSHOT to MYUSER; Grant create session, alter any materialized view to MYUSER; Figured it might have been an issue with the user I was calling the PROC from, but even after changing it to the creator of the Mview and such, still getting the same error.... Now the strange part is, if I login via client app (Toad) and I run the refresh manually as the same user, it works fine, no errors, but then as soon as I try to call it via "Active Batch" workflow automation, it ha
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 Whilst you are here, check out some content from the AskTom team: PL/SQL Brain Teaser: when ora-12008: error in materialized view refresh path ora-01722: invalid number does a COMMIT not commit? Latest Followup You Asked Hi Tom, I 'm using Oracle ora-12008: error in materialized view refresh path ora-01555: snapshot too old Database 10g Enterprise Edition Release 10.2.0.1.0; I got these errors when I tried to refresh my materialized view ; ORA-12008: error in
Oracle Bug Id 13910043
materialized view refresh path ORA-01555: snapshot too old: rollback 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 http://stackoverflow.com/questions/19493106/error-in-materialized-view-refresh-path-permission 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 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 https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:409663900346414175 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 and undo tablespace size to 7gb as per the guidelines in the below link, but i still get this error(snapshot too old error) : http://www.akadia.com/services/ora_optimize_undo.html the below is the exact error message, i am getting BEGIN DBMS_MVIEW.REFRESH('test_info_mv2','C'); END; * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2$" too small ORA-02063: preceding line from OPRD.DSTB.NET ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712 ORA-06512: at line 1 do i still need to increase
error in materialized view refreshpath May 16, 2011 Leave a comment Problem: A materialized view refresh job failed with the following error ORA-12008: error in materialized https://deeparaja.wordpress.com/2011/05/16/ora-12008-error-in-materialized-view-refresh-path/ view refresh path ORA-08102: index key not found, obj# 6104122, file 336, block 675011 (2) ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720 ORA-06512: at line 1 Resolution Identify the index based on its objects id on the error message as below SQL> select OWNER, OBJECT_NAME, object_type, STATUS from dba_objects WHERE OBJECT_ID error in = 6104122; Rebuild the index as below SQL> ALTER INDEX schema.I_tab_col_idx rebuild online; Altered index. Now the mview refresh worked without any problem. Like this:Like Loading... Filed under Oracle Tagged with materialized view, MVIEW, refresh About DeepaRajaI'm a oracle DBA since 2008. I wanted to keep a record of the incidents I encounter at work and how I resolve them. Thought of error in materialized keeping a blog so could be useful for someone else too. Leave a Reply Cancel reply Enter your comment here... Fill in your details below or click an icon to log in: Email (required) (Address never made public) Name (required) Website You are commenting using your WordPress.com account. (LogOut/Change) You are commenting using your Twitter account. (LogOut/Change) You are commenting using your Facebook account. (LogOut/Change) You are commenting using your Google+ account. (LogOut/Change) Cancel Connecting to %s Notify me of new comments via email. Categories Select category APEX Cloud Control Dataguard Mongo MS SQL Server MysQL Netapp Filers Oracle other RAC Uncategorized Unix Recent Posts ORA-12547: TNS:lost contact ORA-01031: insufficient privileges Recovering lost spfile Viewing alert log for oracle database onwindows GoldenGate dirdat directory fillingup Archives April 2016 June 2015 May 2015 April 2015 December 2014 March 2014 January 2014 October 2013 May 2013 March 2013 August 2012 July 2012 June 2012 April 2012 February 2012 January 2012 November 2011 September 2011 July 2011 May 2011 March 2011 February 2011 November 2010 Blogroll Documentation Plugins Suggest Ideas Support Forum Themes WordPress Blog WordPress