Master Note for MVIEW 'ORA-' error diagnosis. For Materialized View CREATE or REFRESH

++++++++++++++++++++++++++++++++++++++++++++++++++++
The complete and the most recent version of this article can be viewed
from My Oracle Support Knowledge Section.

Master Note for MVIEW 'ORA-' error diagnosis.  For Materialized View CREATE or REFRESH (Doc ID 1087507.1)
++++++++++++++++++++++++++++++++++++++++++++++++++++



Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.5 to 11.2.0.2.0 - Release: 10.1 to 11.2
Information in this document applies to any platform.

Purpose

How to use this Doc.

This article has been created to provide a path to existing My Oracle Support articles that have been
written to cover the many possible 'ORA-' related issues that can arise from the creation, refresh and
general maintenance of Materialized Views (also know as - Snapshots / MVIEW).

Please follow the links above or use the error message you would like a possible solution to as the search criteria (Ctrl-f) within this document.

The search will take you to the section that explains the error, and provides links to existing
documents (where they exist) in My Oracle Support. These documents have been written from previous
occurance of the error and show the Cause and Solution in each case.

Which ORA- error message should I use?

The error messages will appear as a single line or part of an error stack. It is a good idea to use the
correct error as some are just high level errors that are not too important initially.
Example 1.
ORA-12028: materialized view type is not supported by master site @RKEK2212.EVA.TAO.SE
Example 2.
ORA-12012: error on auto execute of job 32
ORA-12008: error in materialized view refresh path                  -- These 2 lines are
ORA-01008: not all variables bound                                        -- significant here
ORA-02063: preceding line from DMDPROD
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195

Scope and Application

This is article is intended for DBA's,  Users and Support Engineers who have received an ORA- type error when running, refreshing or creating Materialized Views (MVIEWs).


Master Note for MVIEW 'ORA-' error diagnosis. For Materialized View CREATE or REFRESH


Error List with explanations and links to Oracle Support Articles

1.  ORA-04021: timeout occurred while waiting to lock object %s%s%s%s%s".

Errorstack

ORA-04021: timeout occurred while waiting to lock object %s%s%s%s%s".

Documented error explanation:

ORA-04021 timeout occurred while waiting to lock object %s%s%s%s%s".
Cause: While trying to lock a library object, a time-out occurred.
Action: Retry the operation later.

My Oracle Support Knowledge Solutions:

Note 169139.1   How to analyze ORA-04021 or ORA-4020 errors?

<169139.1>Further Actions:

 There are a few issues in this area which may involve patches, So please create an Sr to progress this ORA-4021 issue.


2.  ORA-16000: database open for read-only access

Errorstack

ORA-02054: transaction 9.31.251080 in-doubt
ORA-16000: database open for read-only access
ORA-02063: preceding line from RECREP2CTMS

Documented error explanation:
 
ORA-16000: database open for read-only access
Cause: The database has been opened for read-only access. Attempts to modify the database using
inappropriate DML or DDL statements generate this error.
Action: In order to modify the database, it must first be shut down and re-opened for read-write access.

My Oracle Support Knowledge Solutions: <276023.1>

Note 276023.1    Create a Distributed Materialized View Based on a Master Table on a Read-Only Database

Further Actions:

 Please create an Sr to progress this ORA- message if the article(s) did not help.


3.  ORA-00942: table or view does not exist

Errorstack

ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 1543
ORA-06512: at line 1

Documented error explanation:

ORA-00942 table or view does not exist

My Oracle Support Knowledge Solutions:

Note 364632.1    Create Materialized View results in : Ora-942

Note 92350.1     ORA-12018, ORA-00942: Upon Fast Refresh of a Materialized View

Note 867042.1    ORA-942 on Materialized View Refresh after Master Mview Recreated on Different Table

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.



4.  ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Errorstack

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Documented error explanation:

 
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Cause: The materialized view did not satisfy conditions for refresh at commit time.
Action: Specify only valid options.

My Oracle Support Knowledge Solutions:
 
Note 279168.1    Unable To Create On Commit Materialized View ORA-12054

Note 420856.1    Error ORA-12054 On Local Mviews Written In ANSI SQL Format

Note 301627.1   Materialized view creation with ON COMMIT fails with ORA-12054 when using DBLINK


Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


5.  ORA-30372: fine grain access policy conflicts with materialized view"

Errorstack

ORA-30372: fine grain access policy conflicts with materialized view"

Documented error explanation:

ORA-30372: fine grain access policy conflicts with materialized view
Cause: A fine grain access control procedure has applied a non-null policy to the query for the
materialized view.
Action: In order for the materialized view to work correctly, any fine grain access control
procedure in effect for the query must return a null policy when the materialized view is being
created or refreshed. This may be done by ensuring that the usernames for the creator, owner, and
invoker of refresh procedures for the materialized view all receive a null policy by the user-written
fine grain access control procedures.

My Oracle Support Knowledge Solutions:

Enhancement Request- 7016507: ALLOW REMOTE MATERIALIZED VIEWS TO BE CREATED AFTER FGAC IS SETUP.

Note 604046.1    Ora-30372: Fine Grain Access Policy Conflicts With Materialized View

Note 386755.1    How To Implement A VPD Policy Working With Materialized Views to Avoid ORA-30372

Note 1065944.1 ORA-12015 or ORA-28133 When Creating Materialized Views If The Base Table Has A FGA Policy

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


6.  ORA-04068: existing state of packages has been discarded

Errorstack

ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "SYS.DBMS_JOB" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_IREFRESH", line 186
ORA-06512: at "SYS.DBMS_IREFRESH", line 369
ORA-06512: at "SYS.DBMS_REFRESH", line 88
ORA-06512: at "SYS.DBMS_REFRESH", line 62
ORA-06512: at "SYS.DBMS_ISNAPSHOT", line 76
ORA-01403: no data found
ORA-06512: at line 1

Documented error explanation:

Error: ORA 4068
Text: existing state of packages%s%s%s has been discarded
-------------------------------------------------------------------------------
Cause: One of errors 4060 - 4067 when attempt to execute a stored procedure.
Action: Try again after proper re-initialization of any application's state.

My Oracle Support Knowledge Solutions:

Note 742355.1    How creating an mview with the NEXT option automatically creates a refresh job

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


7.  ORA-01031: insufficient privileges

Errorstack

ORA-01031: insufficient privileges

Documented error explanation:

ORA-01031: insufficient privileges
Cause: An attempt was made to change the current username or password without the appropriate privilege.
This error also occurs if attempting to install a database without the necessary operating system
privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the
necessary privilege at a higher label than the current login.
Action: Ask the database administrator to perform the operation or grant the required privileges. For
Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label,
ask the database administrator to regrant the privilege at the appropriate label.

My Oracle Support Knowledge Solutions:

Note 1027174.6   Privileges To Refresh A Snapshot Or Materialized View

Note 1079983.6   Create Local Materialized View With Query Rewrite Option Fails with ORA-1031 Insufficient Privileges.

Note 263650.1   Creating Materialized View in a Different Schema Fails with ORA-01031

Note 394248.1   Create Materialized View fails with ORA-01031 in Logical Standby Environment

Note 433860.1   ORA-1031 creating an ON COMMIT MATERIALIZED VIEW on another Schema

Note 334722.1   ORA-01031 'Insufficient Privileges' Error When Enabling Query Rewrite on Another Schema's Materialized View

Note 215462.1 Dropping a Materialized View Log in a different schema fails with error ORA-01031

Note 215463.1 ORA-01031 Insufficient Privileges When Create A Materialized View Log In Different Schema:

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


8. ORA-12096: error in materialized view log on %s . %s

Errorstack

ORA-12096: error in materialized view log on %s . %s
ORA-00932: inconsistent datatypes: expected %s got %s

Documented error explanation:

Error: ORA-12096 (ORA-12096)
Text: error in materialized view log on %s . %s
---------------------------------------------------------------------------
Cause: There was an error originating from this materialized view log.  One possible cause is that schema
redefinition has occurred on the master table and one or more columns in the log is now a different
type than corresponding master column(s). Another possible cause is that there is a problem accessing the underlying materialized view log table.
Action: Check further error messages in stack for more detail about the cause. If there has been schema redefinition, drop the materialized view log and recreate it.

My Oracle Support Knowledge Solutions:

None available for this error code combination.

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


9. ORA-06502: PL/SQL: numeric or value error

Errorstack

ORA-12012: error on auto execute of job 3
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

Documented error explanation:

ORA-06502: PL/SQL: numeric or value error

My Oracle Support Knowledge Solutions:

Note 370503.1   Complete Materialized View Refresh reports ORA-06502 in Setup Phase:

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


10. ORA-00001: unique constraint (JJHS.CPT_PROD_VOL_XP) violated

Errorstack

ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (JJHS.CPT_PROD_VOL_XP) violated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

Documented error explanation:

Error: ORA-12008 (ORA-12008)
Text: error in materialized view refresh path
---------------------------------------------------------------------------
Cause: Table SNAP$_mview_name reads rows from the view MVIEW$_mview_name, which is a view on
the master table (the master may be at a remote site). Any error in this path will cause this error at refresh time.
For fast refreshes, the table master_owner.MLOG$_master is also referenced.
Action: Examine the other messages on the stack to find the problem. See if the objects SNAP$_mview_name,
MVIEW$_mview_name, mowner.master@dblink, mowner.MLOG$_master@dblink still exist.

My Oracle Support Knowledge Solutions:

Note 67424.1   Materialized View Triggers, Constraints and Longs.

Note 731707.1   How to create and refresh a ROWID MView with a PK on the master table.

Note:189567.1   DBMS_JOB Snapshot Refresh Fails with ORA-12008 Error in Materialized View Refresh Path

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


11. ORA-12020: materialized view %s is not registered

Errorstack

ORA-12020: materialized view %s is not registered
ORA-06512: in "SYS.DBMS_SNAPSHOT_UTL", line 1650
ORA-06512: in "SYS.DBMS_SNAPSHOT", line 2752
ORA-06512: in "SYS.DBMS_SNAPSHOT", line 2739
ORA-06512: in line 2

Documented error explanation:

Error: ORA-12020 (ORA-12020)
Text: materialized view %s is not registered
---------------------------------------------------------------------------
Cause: An attempt was made to unregister a materialized view that is not registered.
Action: No action required.

My Oracle Support Knowledge Solutions:

Note 258634.1     Materialized View registration at Master Site

Note 67371.1      How to Manually Register/Unregister Snapshots

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


12.  ORA-04052: error occurred when looking up remote object

Errorstack

ORA-04052: error occurred when looking up remote object

Documented error explanation:

Error: ORA 4052
Text: error occurred when looking up remote object %s%s%s%s%s
-------------------------------------------------------------------------------
Cause: An error has occurred when trying to look up a remote object.
Action: Fix the error. Make sure the remote database system has run KGLR.SQL to create necessary
views used for querying/looking up objects stored in the database.

My Oracle Support Knowledge Solutions:

Note 5671074.8   Bug 5671074 - ORA-4052/ORA-3106 on create / refresh of materialized view

Note 832436.1   Cannot Create MView Based on Data from a Remote Database:

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


13.  ORA-30361 unrecognized string type

Errorstack

ORA-30361 unrecognized string type

Documented error explanation:

Error: ORA-30361
Text: unrecognized string type
---------------------------------------------------------------------------
Cause: An internal Oracle error occured.
Action: Report the problem through your normal support channels.

My Oracle Support Knowledge Solutions:

Note 331669.1   Creation of Mview with connect_by_root fails with ora-30361

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


14.  ORA-32401 materialized view log on %s . %s does not have new values

Errorstack

ORA-32401 materialized view log on %s . %s does not have new values

Documented error explanation:

Error: ORA-32401 (ORA-32401)
Text: materialized view log on %s . %s does not have new values
---------------------------------------------------------------------------
Cause: Materialized view log on the indicated table does not have new values information.
Action: Add new values to materialized view log using the ALTER MATERIALIZED VIEW LOG command.

My Oracle Support Knowledge Solutions:

Note 174079.1  ORA-32401 materialized view log on %s . %s does not have new values

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


15.  ORA-01405: fetched column value is NULL

Errorstack

ORA-00604: error occurred at recursive SQL level 1
ORA-01405: fetched column value is NULL

Documented error explanation:

Error:  ORA-01405: fetched column value is NULL

My Oracle Support Knowledge Solutions:

Note 945737.1   ORA-604, ORA-1405 After Truncating A Partitioned Table:

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


16.  ORA-02067: transaction or savepoint rollback required

Errorstack

02067, 00000, "transaction or savepoint rollback required"

Documented error explanation:

ORA-02067: transaction or savepoint rollback required
Cause: A failure (typically a trigger or stored procedure with multiple remote updates) occurred such that the all-or-nothing execution of a previous Oracle call cannot be guaranteed.
Action: rollback to a previous savepoint or rollback the transaction and resubmit.

My Oracle Support Knowledge Solutions:

Note 254593.1   Differences Between Rowid & Primary Key Materialized Views:

Note 395674.1   Ora-12028 Materialized View Type Is Not Supported By Master Site

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.



17. ORA-01555: snapshot too old: rollback segment number 3 with name "_____" too small

Errorstack

ORA-12012: error on auto execute of job 31030
ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3$" too small
ORA-02063: preceding line from G2PROD
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

Documented error explanation:

ORA-01555: snapshot too old (rollback segment too small)
Cause: rollback records needed by a reader for consistent read are overwritten by other writers

My Oracle Support Knowledge Solutions:

Note.464524.1   Ora-1555 Dbms_Refresh.Refresh or Dbms_Mview.Refresh On a Distributed Refresh:

Note.258252.1   MATERIALIZED VIEW REFRESH Locking, Performance, Monitoring

Note.269814.1   ORA-01555 Using Automatic Undo Management - Causes and Solutions

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


18. ORA-04063: package body "SYS.DBMS_SNAP_INTERNAL" has errors

Errorstack

ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.DBMS_SNAP_INTERNAL" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 782
ORA-06512: at "SYS.DBMS_REPCAT", line 548
ORA-06512: at line 2

Documented error explanation:

My Oracle Support Knowledge Solutions:

Note.418445.1   ORA-04063 occurs while creating replication group

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


19.  ORA-12015: cannot create a fast refresh materialized view from a complex query

Errorstack

ORA-12015: cannot create a fast refresh materialized view from a complex query

Documented error explanation:

ORA-12015: cannot create a fast refresh materialized view from a complex query
Cause: Neither ROWIDs and nor primary key constraints are supported for complex queries.
Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE option or create a simple
materialized view.

My Oracle Support Knowledge Solutions:

Note.179466.1   Diagnosing ORA-12015 fast refresh materialized view / complex queries:

Note.420856.1   Error ORA-12054 On Local Mviews Written In ANSI SQL Format

Note.402843.1   Ora-12015 Creating Materialized View Based On A Synonym

Note.373089.1   Creating a Materialized View Based on a View With Refresh Fast Receives ORA-12015, and Creating It With Refresh Force Receives ORA-01723

Note.761241.1   Error Ora-12015 When Create Simple Materialized View

Note.1065944.1   ORA-12015 or ORA-28133 When Creating Materialized Views If The Base Table Has A FGA Policy

Note.242004.1   ORA-12015 Creating a materialized view fails when cursor_sharing=FORCE

Note.141093.1   Create Materialized View fails with ORA-12015

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.

20.  ORA-02292: integrity constraint (string.string) violated - child record found

Errorstack

ORA-02091: transaction rolled back
ORA-02292: integrity constraint (IBIS.FK_KFZERMBOGEN_2_KFZZBEZERM) violated - child record found
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
ORA-06512: at line 1

Documented error explanation:

ORA-02292: integrity constraint (string.string) violated - child record found
Cause: attempted to delete a parent key value that had a foreign key dependency.
Action: delete dependencies first then parent or disable constraint.

My Oracle Support Knowledge Solutions:

Note.407016.1   ORA-02292 on mview refresh:

Note.67424.1   Materialized View Triggers, Constraints and Longs:

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


21.  ORA-012028  materialized view type is not supported by master site string

Errorstack

ORA-12028: materialized view type is not supported by master site@RK_LINK.GROUP.CO

Documented error explanation:

ORA-12028: materialized view type is not supported by master site string
Cause: Pre-Oracle8 master sites are not able to support primary key or subquery materialized views that are able to perform a fast refresh.
Action: Create a ROWID materialized view or use a master table from an Oracle8 site.

My Oracle Support Knowledge Solutions:

Note.1059547.1   How to deal with ORA-12028 reported when creating a Fast Refreshable Remote Mview:

Note.395674.1   Ora-12028 Materialized View Type Is Not Supported By Master Site

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.


22.  ORA-12008: error in materialized view refresh path

Errorstack

ORA-12012: error on auto execute of job 32
ORA-12008: error in materialized view refresh path
ORA-01008: not all variables bound
ORA-02063: preceding line from DMDPROD
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195

Documented error explanation:

None directly available


My Oracle Support Knowledge Solutions:

This is usually down to a database setup issue, so please contact Oracle Support to progress this ORA-01008
error message.

Further Actions:

Please create an Sr to progress this ORA- message if the article(s) did not help.

23. ORA-06512: at "SYS.DBMS_SNAPSHOT", line 9999, ( at "SYS.DBMS_SNAPSHOT" , at "SYS.DBMS_REFRESH")

Errorstack
...
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

Documented error explanation:
ORA-06512: at stringline string
Cause: Backtrace message as the stack is unwound by unhandled exceptions.
Action: Fix the problem causing the exception or write an exception handler for this condition.
Or you may need to contact your application administrator or DBA.

My Oracle Support Knowledge Solutions:
Not really applicable as this message is the end result of the error. The error that is relevant will be higher up
the list(stack) of errors produced.

Further Actions:
Please review the other errors provided as the ora-06512 is a "catch all" type of error which means an error
has occurred in the refresh code you are running. The specific reason will be the "ORA-" errors higher up above this
ORA-06512 line.   Please create an Sr to progress this ORA- message if this article did not help.

24.  ORA-22992: cannot use LOB locators selected from remote tables

Errorstack
ORA-22992: cannot use LOB locators selected from remote tables.

Documented error explanation:
Error: ORA-22992
Cause: A remote LOB column cannot be referenced.
Action: Remove references to LOBs in remote tables.

My Oracle Support Knowledge Solutions:
Note.459557.1   ORA-1406 Fetched Column Value was Truncated When Selecting Remote Column into Local BLOB Variable:
Note.312300.1   Create Materialized View Fails with Ora-22992 error

Further Actions:
Please create an Sr to progress this ORA- message if the article(s) did not help.


Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

News and Troubleshooting tips for Oracle Database and Enterprise Manager

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today