I need to modify Materialized view query . Is is possible to do the same without droping and recreating it.
No, you cannot alter the query of a materialized view without dropping it. The CREATE MATERIALIZED VIEW syntax does not support that feature. The ALTER MATERIALIZED VIEW is used to modify an existing materialized view in one or more of the following ways: To change its storage characteristics.
The privileges required to alter a materialized view should be granted directly, as follows: The materialized view must be in your own schema, or you must have the ALTER ANY MATERIALIZED VIEW system privilege.
You can use a materialized view in any SQL query by referencing the materialized view name as the data source, like a table or standard view. When a query accesses a materialized view, it sees only the data that is stored in the materialized view as of its most recent refresh.
Description. ALTER MATERIALIZED VIEW changes various auxiliary properties of an existing materialized view. You must own the materialized view to use ALTER MATERIALIZED VIEW . To change a materialized view's schema, you must also have CREATE privilege on the new schema.
No, you cannot alter the query of a materialized view without dropping it.
The CREATE MATERIALIZED VIEW syntax does not support that feature.
The ALTER MATERIALIZED VIEW is used to modify an existing materialized view in one or more of the following ways:
See Oracle 12c Release 1 Manual for:
CREATE MATERIALIZED VIEW syntax: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6002.htm#i2145767
ALTER MATERIALIZED VIEW syntax: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_2002.htm#SQLRF00808
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With