Create a Distributed Materialized View Based on a Master Table on a Read-Only Database
(Doc ID 276023.1)
Last updated on OCTOBER 26, 2024
Applies to:
Oracle Database Cloud Exadata Service - Version N/A and laterOracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 19.6.0.0.0 [Release 9.2 to 19]
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.
Goal
This article provides a solution for creating a Materialized View (MView) based on a remote table in a read-only database. such as a standby database.
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
Under certain conditions, such operation may result in the following error messages:
Oracle 9i: ORA-00600: internal error code, arguments: [12840]
Oracle 10g and later: ORA-04063: table "..." has errors or ORA-16000: database open for read-only access
From Oracle9i onwards, the database automatically registers information about a remote Materialized View.
If the Materialized View is fast refreshable, then then it must be registered at the master database site (SYS.SLOG$) to allow fast refreshes and maintain the Materialized View Log at the master ste. This will fail if the master database is open in read-only mode.
If the Materialized View is not fast refreshable (ie. "complex"), it can be registered at the master database site (SYS.REG_SNAP$). This is for informational purposes and the registration information is not needed to refresh the Materialized View. In this case, the Materialized View is registered at its Materialized View site, not at the (read-only) master site.
From the above, it is clear that any materialzed View that is fast refreshable cannot be registered with a read-only database, while a Materialized View that is not fast refreshable can.
The decision if a Materialized View is fast refreshable is made by the RDBMS based on the MView defining query, not the actual refresh method declared when creating the MView.
Solution
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Goal |
| Solution |
| General Restrictions on Fast Refresh |
| References |