This content has been marked as final. Show 2 replies
Are these schemas in the same database? You don't mention multiple databases. You wouldn't use a materialized view to replicate data from one schema to another. You might want to aggregate data from a table in one schema in a materialized view in a different schema but that would be pretty odd-- you'd generally want the aggregated data in the same schema that the detail data is in.
I am new to materialized views, and I was trying to read some documentation and it got confusing, I have a requirement to create a materialized view in schema targ on table s in sour schema and I want it to refresh every 20 minutes. I also want a materialized view log?
It you want the MV to refresh every 20 minutes you use the START WITH and NEXT clauses
START WITH Clause
Specify a datetime expression for the first automatic refresh time.
Specify a datetime expression for calculating the interval between automatic refreshes.
Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, then the database determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view. If you specify a START WITH value but omit the NEXT value, then the database refreshes the materialized view only once. If you omit both the START WITH and NEXT values, or if you omit the create_mv_refresh entirely, then the database does not automatically refresh the materialized view.
. . .
Creating Primary Key Materialized Views: Example The following statement creates the primary key materialized view catalog on the sample table oe.product_information:
CREATE MATERIALIZED VIEW catalog
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/4096
WITH PRIMARY KEY
AS SELECT * FROM product_information;
You would use 1/(24 * 3) = 1/72 for 1/3 hour (20 min.)
I need help with what the syntax would be for the materialized view and materialized view log. AND also where would the materialized view log be created(what schema).
Have you tried reviewing/creating the examples in the SQL Language doc?