How to set all_rows for a materialized view in oracle?

@haylee.mertz 

To set all_rows for a materialized view in Oracle, you can use the following SQL statement:

1
2
3
4
5
6

CREATE MATERIALIZED VIEW my_materialized_view 
BUILD IMMEDIATE 
REFRESH COMPLETE
ENABLE QUERY REWRITE 
AS 
SELECT * FROM my_table;

In this statement, the ALL ROWS option is set by default when creating a materialized view. The ENABLE QUERY REWRITE option allows Oracle to rewrite queries to use the materialized view when possible.

You can also explicitly set the ALL ROWS option when refreshing the materialized view like this:

1

EXEC DBMS_MVIEW.REFRESH('my_materialized_view', 'C', atomic_refresh=>FALSE, refresh_after_errors=>TRUE);

By setting atomic_refresh to FALSE and refresh_after_errors to TRUE, you are instructing Oracle to refresh the materialized view without any errors and to allow querying all rows in the materialized view.