Materialized views allow you to maintain copies of remote data on your local node. The copies can be updatable with the Advanced Replication feature and are read-only without this feature.
What is called master table?
The tables in the query are called master tables (a replication term) or detail tables (a data warehouse term).This reference uses "master tables" for consistency.
What is master database?
The databases containing the master tables are called the master databases.
For data warehousing purposes, the materialized views commonly created are materialized aggregate views, single-table materialized aggregate views, and materialized join views. In a data warehousing environment, all master tables must be local.
What are Privileges required to create Materialized view in your schema?
CREATE MATERIALIZED VIEW or CREATE SNAPSHOT system privilege and either the CREATE TABLE or CREATE ANY TABLE system privilege.
You must also have access to any master tables of the materialized view that you do not own, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.
What are Privileges required to create Materialized view in another user schema?
You need CREATE ANY MATERIALIZED VIEW or CREATE ANY SNAPSHOT system privilege and access to any master tables of the materialized view that you do not own, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.
The owner of the materialized view must have the CREATE TABLE system privilege. Owner should be able to access any master tables of the materialized view that the schema owner does not own and to any materialized view logs defined on those master tables, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.
To create the materialized view with query rewrite enabled, The owner of the master tables must have the QUERY REWRITE system privilege.
If you are not the owner of the master tables, you must have the GLOBAL QUERY REWRITE system privilege.
If the schema owner does not own the master tables, then the schema owner must have the GLOBAL QUERY REWRITE privilege.
The user whose Schema in which materialized view is present must have sufficient quota in the target tablespace to store the materialized view's master table and index, or must have the unlimited tablespace system privilege.
Oracle create few object to maintain materialized view's data when materialized view are created, this object are one internal table, at least one index & may create one view in the schema of the materialized view.
