Oracle 12c R2 Error Codes and Solution Suggestions from ORA-12000 to ORA-12100
- ORA-12001: cannot create log: table ‘string’ already has a trigger
Cause: Materialized view logs are filled by a trigger on the master table. That trigger cannot be created.
Action: To create a materialized view log, drop the current trigger on the master.
Cause: There was no materialized view log on the master table.
Action: Create a materialized view log on the master table.
Cause: The database object with the given owner and name was not found.
Action: Verify input and retry.
Cause: The materialized view log does not exist or cannot be used. PCT refresh is also not enabled on the materialized view
Action: Use just REFRESH, which will reinstantiate the entire table. If a materialized view log exists and the form of the materialized view allows the use of a materialized view log or PCT refresh is possible after a given set of changes, REFRESH FAST will be available starting the next time the materialized view is refreshed.
Cause: An attempt was made to schedule an automated materialized view refresh for a time in the past.
Action: Choose a time in the future instead.
Cause: The CREATE MATERIALIZED VIEW .. or CREATE MATERIALIZED VIEW LOG .. REUSE command was given inconsistent parameters immediately after the REUSE.
Action: Examine the other messages on the stack to find the problem.
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.
- ORA-12009: materialized views may not contain long columns
- ORA-12010: cannot create materialized view log on table owned by SYS
Cause: An attempt was made to create a materialized view log on the table owned by SYS. CREATE MATERIALIZED VIEW LOG attempts to create a trigger on the table, but triggers can not be created on SYS tables.
Action: Do not create a materialized view log on SYS tables.
Cause: An error was caught in dbms_ijob.run from one or more jobs which were due to be run.
Action: Look at the alert log for details on which jobs failed and why.
Cause: An error was caught while doing an automatic execution of a job.
Action: Look at the accompanying errors for details on why the execute failed.
- ORA-12013: updatable materialized view must be simple enough and have a materialized view log on its master table to do fast refresh
Cause: The updatable materialized view query contained a JOIN, SUBQUERY, UNION, CONNECT BY, ORDER BY, or GROUP BY clause, or the master table did not have a materialized view log.
Action: Make the materialized view simpler. If a join is really needed, make multiple simple materialized views then put a view on top of them. Make sure a materialized view log is created for the master table.
Cause: The CREATE MATERIALIZED VIEW LOG command was issued with the WITH PRIMARY KEY option and the master table did not contain a primary key constraint or the constraint was disabled.
Action: Reissue the command using only the WITH ROWID option, create a primary key constraint on the master table, or enable an existing primary key constraint.
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.
Cause: The query that instantiates the materialized view did not include all of the columns in the master’s primary key constraint.
Action: Include all of the master’s primary key columns in the materialized view query or create a ROWID materialized view.
Cause: An attempt was made to convert the primary key of a materialized view to a ROWID materialized view.
Action: Conversion of a primary key materialized view to a ROWID materialized view is not supported. Create a new materialized view with ROWIDs or drop and recreate the materialized view with ROWIDs.
Cause: The refresh operations for the indicated materialized view could not be regenerated due to errors.
Action: Correct the problem indicated in the following error messages and repeat the operation.
Cause: An attempt was made to create a materialized view or a materialized view log on a remote synonym which is unsupported.
Action: Do not create a materialized view or materialized view log on a remote synonym.
Cause: An attempt was made to unregister a materialized view that is not registered.
Action: No action required.
Cause: The materialized view indicated is no longer valid.
Action: Contact Oracle Customer Support.
Cause: Materialized view log on the indicated table already has ROWID information.
Action: No action required.
Cause: The specified ROWID materialized view did not have the required index on the ROWID column of its underlying table.
Action: Drop and recreate the materialized view.
Cause: Materialized view log on the indicated table does not have primary key information.
Action: Add primary keys to the materialized view log using the ALTER MATERIALIZED VIEW command.
Cause: Materialized view log on the indicated table already has primary key columns.
Action: No action required.
Cause: One or more of the specified filter columns did not exist or was a primary key column or a primary key based object identifier.
Action: Ensure that all specified filter columns exist in the master table and ensure that primary key columns or primary key based object identifiers are not included in the list of filter columns.
Cause: One or more of the specified filter columns were already being recorded in the materialized view log.
Action: Describe the materialized view log table and reissue the SQL command with the filter columns that are already being recorded in the materialized view log.
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.
Cause: An attempt was made to use LOB columns as filter columns.
Action: Remove LOB columns from the filter columns list and retry command.
Cause: The materialized view log did not exist or did not log the information needed by the materialized view to perform a fast refresh.
Action: Ensure that the materialized view log exists and logs the necessary information. – For ROWID materialized views, the master table’s ROWID must be logged. – For primary key materialized views, the master table’s primary key columns must be logged. – For subquery materialized views, the filter columns, primary key, and ROWID values must be logged. – For object materialized views, object id must be logged.
Cause: The materialized view log either did not have primary key columns logged, or the timestamp associated with the primary key columns was more recent than the last refresh time.
Action: A complete refresh is required before the next fast refresh. Add primary key columns to the materialized view log, if required.
Cause: The materialized view log either does not have ROWID columns logged, or the timestamp associated with the ROWID columns is more recent than the last refresh time.
Action: A complete refresh is required before the next fast refresh. Add ROWID columns to the materialized view log, if required.
Cause: The materialized view log either did not have filter columns logged, or the timestamp associated with the filter columns was more recent than the last refresh time.
Action: A complete refresh is required before the next fast refresh. Add filter columns to the materialized view log, if required.
Cause: The materialized view log was younger than the last refresh.
Action: A complete refresh is required before the next fast refresh.
Cause: The materialized view log did not exist or could not be used.
Action: Use just REFRESH, which will reinstantiate the entire table. If a materialized view log exists and the form of the materialized view allows the use of a materialized view log, REFRESH FAST will be available starting the next time the materialized view is refreshed.
Cause: The updatable materialized view log was not empty. The updatable materialized view log must be empty before an updatable rowid materialized view can be altered to a primary key materialized view.
Action: Ensure that updatable materialized view log is empty by refreshing the materialized view before converting the updatable ROWID materialized view to a primary key materialized view.
Cause: An attempt was made to import a materialized view exported by an unknown export version (e.g., from a newer release than the importing site)
Action: Re-export the file using a version of export known by the importing site.
- ORA-12038: string literal has unexpected length
- ORA-12039: unable to use local rollback segment “string“
Cause: A local rollback segment was specified in the CREATE MATERIALIZED VIEW command, but automatic refresh parameters were not specified. Therefore a refresh group was not created to automatically refresh the materialized view and the local rollback segment can’t be registered for future use.
Action: Either supply the automatic refresh parameters so that a refresh group will be created or do not specify a local rollback segment.
Cause: An attempt was made to specify master rollback segment in the current operation. The master site of the current materialized view does not allow users to specify a rollback segment to be used for materialized view operations. This feature is only supported by Oracle8 or later master sites.
Action: Do not specify a master rollback segment in the current operation or choose a new master site.
Cause: Index-organized tables do not have ROWIDs. Therefore a materialized view log that records the ROWIDs of an index-organized table could not be created.
Action: Do not include the WITH ROWID option when using the CREATE MATERIALIZED VIEW command and do not include the ADD ROWID option when using the ALTER MATERIALIZED VIEW command if the master table is index-organized.
Cause: An attempt was made to alter job_queue_processes in single process mode.
Action: Do not attempt to set job_queue_processes in single process mode.
Cause: An invalid option was used in a CREATE MATERIALIZED VIEW statement.
Action: Specify only valid options.
Cause: An invalid option was used in a CREATE MATERIALIZED VIEW LOG statement.
Action: Specify only valid options.
Cause: An invalid option was used in an ALTER MATERIALIZED VIEW LOG statement.
Action: Specify only valid options.
Cause: REFRESH USING TRUSTED CONSTRAINTS specified in ALTER MATERIALIZED VIEW or CREATE MATERIALIZED VIEW of a remote materialized view
Action: remove this attribute from ALTER/ CREATE MATERIALIZED VIEW DDL
Cause: PCT refresh is either not enabled on this materialized view or not possible after set of the base table changes since last refresh.
Action: Use just REFRESH FORCE, which will reinstantiate the entire table and try to pick the best refresh method on the materialized view Do explain_mview to look at the cause why PCT refresh is not possible on this materialized view
Cause: Some problem occurs during refresh of multiple materialized views in atomic mode. The materialized view whose refresh failed has raised this error.
Action: Examine the other messages on the stack to find the refresh problem.
Cause: ON COMMIT refresh attribute, incompatible with other refresh options such as automatic periodic refresh, was specified.
Action: Specify only valid options.
Cause: Either ROWIDs of certain tables were missing in the definition or the inner table of an outer join did not have UNIQUE constraints on join columns.
Action: Specify the FORCE or COMPLETE option. If this error is got during creation, the materialized view definition may have be changed. Refer to the documentation on materialized views.
Cause: The list of objects in the FROM clause of the definition of this materialized view had some dependencies upon each other.
Action: Refer to the documentation to see which types of nesting are valid.
Cause: The materialized view did not satisfy conditions for refresh at commit time.
Action: Specify only valid options.
- ORA-12055: materialized view definition contains cyclic dependencies with existing materialized views
Cause: The materialized view query definition introduced a cyclic dependency with existing materialized view.
Action: Modify the materialized view query definition.
Cause: The NEVER REFRESH option may not be used under the following conditions: * The materialized view is updatable * The materialized view refreshes ON COMMIT * Automatic refresh options are specified
Action: For updatable materialized views, reissue the SQL command using REFRESH FORCE, REFRESH FAST, or REFRESH COMPLETE. For read-only materialized views, reissue the SQL command using ON DEMAND.
Cause: The status of the materialized view or zonemap was invalid and an attempt was made to fast refresh it.
Action: Perform a complete refresh of the materialized view or zonemap.
Cause: An attempt was made to use the prebuilt tables.
Action: Reissue the SQL command using BUILD IMMEDIATE or BUILD DEFERRED.
Cause: The specified prebuilt table did not exist.
Action: Reissue the SQL command using BUILD IMMEDIATE, BUILD DEFERRED, or ensure that the prebuilt table exists.
Cause: The number of columns or the type or the length semantics of a column in the prebuilt table did not match the materialized view definition query.
Action: Reissue the SQL command using BUILD IMMEDIATE, BUILD DEFERRED, or ensure that the prebuilt table matches the materialized view definition query.
Cause: An invalid option was used in an ALTER MATERIALIZED VIEW statement.
Action: Specify only valid options.
Cause: A transaction from the client site was received out of sequence. This implies that one or more transactions were missing.
Action: Ensure that the transaction queue at the client site is valid and has not been corrupted.
Cause: The current transaction from the client site could not be applied to the master site.
Action: Ensure that the client site is still valid and that it has not been dropped from the master site.
Cause: The client site was attempting to perform a refresh with an invalid refresh sequence.
Action: Perform a complete refresh to synchronize the refresh sequence number.
Cause: The specified refresh group did not exist at the master site.
Action: Ensure that the client site is still valid and that it has not been dropped from the master site.
Cause: The Replication API does not support the following options and types of materialized view: o ROWID materialized views o REFRESH ON COMMIT o ON PREBUILT TABLE o BUILD DEFERRED o NEVER REFRESH o ENABLE QUERY REWRITE
Action: Create a PRIMARY KEY materialized view using REFRESH WITH PRIMARY KEY and/or remove the invalid options.
Cause: The refresh group being instantiated did not contain any materialized views.
Action: Modify the template to include at least one materialized view.
Cause: The updatable materialized view was missing the updatable materialized view log required to track updates made to the materialized view.
Action: Create the updatable materialized view log.
Cause: Only materialized views can be offline instantiated. The object being offline instantiated was not a valid materialized view.
Action: Remove the object from the template or replace the object with a valid materialized view.
Cause: Offline instantiation does not support materialized views using the following options: o ON PREBUILT TABLE o BUILD DEFERRED
Action: Remove the invalid options.
Cause: Offline instantiation requires materialized view definition queries to observe the following constraints: o The database link that will be used by the materialized view site to connect to the master site must be included with each master table referenced in the query. o All master tables referenced must be located at the local site. References to other sites are not allowed.
Action: Modify the materialized view definition query.
Cause: The updatable materialized view was missing the updatable materialized view log required to track updates made to the materialized view.
Action: Create an updatable materialized view log.
Cause: An out-of-sequence request was made and it cannot be processed.
Action: Try again with a valid request.
Cause: An attempt was made to access an invalid memory region.
Action: Reconnect and try the command again.
Cause: An attempt was made to access an invalid field or object in the Java Virtual Memory.
Action: Retry the request.
Cause: The result set threshold or LOB threshold value is not supported.
Action: Specify a threshold value below 64K.
Cause: Temporary updatable materialized view log was not created or was dropped.
Action: Re-create the temporary updatable materialized view log. Warning: This will cause a complete refresh of the materialized view.
Cause: Refresh result set sent through client method REFRESH_REQ_RESULT returned an unknown value.
Action: Re-issue the refresh request.
Cause: Result cache created during materialized view refresh was not invalidated at end.
Action: Do not set this as an event.
- ORA-12080: Buffer cache miss for IOQ batching
- ORA-12081: update operation not allowed on table “string“.”string“
Cause: An attempt was made to update a read-only materialized view.
Action: No action required. Only Oracle is allowed to update a read-only materialized view.
Cause: An attempt was made to create an index-organized materialized aggregate view or an index-organized updatable ROWID materialized view. This is not supported.
Action: Try to create the materialized view without the index organization clause.
Cause: An attempt was made to drop a materialized view using a command other than DROP MATERIALIZED VIEW.
Action: Use the DROP MATERIALIZED VIEW command.
Cause: An attempt was made to alter a materialized view using a command other than ALTER MATERIALIZED VIEW.
Action: Use the ALTER MATERIALIZED VIEW command.
Cause: Materialized view log on the indicated table already has object id information.
Action: No action required.
Cause: The CREATE MATERIALIZED VIEW LOG command was issued with the WITH OBJECT ID option and the master table is not an object table.
Action: Either specify the name of an object table, or remove the WITH OBJECT ID clause.
Cause: An attempt was made to online redefine a table owned by SYS or SYSTEM.
Action: Do not attempt to online redefine a table owned by SYS or SYSTEM.
Cause: An attempt was made to online redefine a table containing a LONG column, an ADT column, or a FILE column.
Action: Do not attempt to online redefine a table containing a LONG column, an ADT column, or a FILE column.
Cause: An attempt was made to online redefine a table that does not have a primary key defined on it.
Action: Do not attempt to online redefine a table that does not have a primary key defined on it.
Cause: An attempt was made to online redefine a table that is either a temporary table, an IOT overflow table, a table with fine-grained access control or row-level security enabled, a CDC change table, or a table with a CDC change table defined on it.
Action: Do not attempt to online redefine a table that is not supported by online redefinition.
Cause: An attempt was made to online redefine a table that had materialized views defined on it or had a materialized view log defined on it or is a master.
Action: Drop all materialized views and materialized view logs before attempting to online redefine the table.
Cause: An attempt was made to online redefine a table that is either a materialized view or a replicated table.
Action: Do not attempt to online redefine a table that is either a materialized view or a replicated table.
Cause: The table is not the interim table of the corresponding table to be online redefined.
Action: Pass in the valid interim table.
Cause: There was an error during the online redefinition process.
Action: Abort the online redefinition process.
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.
Cause: There are some changes (i.e., conventional DML, direct load, partition maintenance operation) in the master tables during materialized view refresh.
Action: Refresh the affected materialized views again.
Cause: An attempt was made to issue a COMMENT ON TABLE statement on a materialized view.
Action: Issue a COMMENT ON MATERIALIZED VIEW statement instead.
- ORA-12099: Don’t go into fast table scan (kdst) mode
- ORA-12100: materialized view log on “string“.”string” already has sequence
Cause: Materialized view log on the indicated table already has sequence information.
Action: No action required.
Cause: The number of columns or the type or the length semantics of a column in the materialized view did not match the materialized view base table.
Action: Ensure that the materialized view column list matches with the base table.