Synonyms, Database Links and Materialized Views

Oracle DBA Certification 13 min min read Updated: Nov 16, 2026 Intermediate
Synonyms, Database Links and Materialized Views
Intermediate Topic 3 of 3

Synonyms

A synonym is an alias for an object. It hides the owner name, so applications do not need to say shop.orders.

sql CREATE SYNONYM orders FOR shop.orders; -- private synonym CREATE PUBLIC SYNONYM orders FOR shop.orders; -- visible to all users SELECT * FROM orders;
Good to know: A synonym does not give access. The user still needs GRANT SELECT on the real table.

Database Links

A database link lets you query a table in another Oracle database as if it were local.

sql CREATE DATABASE LINK hq_link CONNECT TO hq_reader IDENTIFIED BY "HqPwd#1" USING 'HQ_DB'; -- an alias from tnsnames.ora SELECT COUNT(*) FROM customers@hq_link;
TipWhy
Use a dedicated read-only user for the linkLimits damage if the link is abused
Avoid joining a huge remote table to a huge local tableAll data crosses the network
Check links regularly with SELECT * FROM dba_db_linksOld links are a security risk

Materialized Views

A normal view runs its query every time. A materialized view (MV) stores the result like a real table and refreshes it when you say. Great for slow reports and summaries.

sql CREATE MATERIALIZED VIEW shop.mv_sales_by_city BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT c.city, COUNT(*) AS orders_count FROM shop.orders o JOIN shop.customers c ON c.customer_id = o.customer_id GROUP BY c.city; SELECT * FROM shop.mv_sales_by_city;

Refresh Methods

MethodWhat happens
COMPLETERebuilds the whole result. Simple, can be slow.
FASTApplies only the changes since the last refresh. Needs a materialized view log.
FORCEFAST if possible, otherwise COMPLETE
TimingMeaning
ON DEMANDYou refresh it when needed (or on a schedule)
ON COMMITRefreshes after each commit on the base tables

Fast Refresh Example

sql CREATE MATERIALIZED VIEW LOG ON shop.customers WITH PRIMARY KEY; CREATE MATERIALIZED VIEW shop.mv_customers REFRESH FAST ON DEMAND AS SELECT * FROM shop.customers;

Refresh It

sql BEGIN DBMS_MVIEW.REFRESH('SHOP.MV_SALES_BY_CITY', method => 'C'); -- C = complete DBMS_MVIEW.REFRESH('SHOP.MV_CUSTOMERS', method => 'F'); -- F = fast END; /
Automate it: Schedule the refresh with DBMS_SCHEDULER (we cover it in the Automation section) or add START WITH SYSDATE NEXT SYSDATE + 1/24 to refresh hourly.

Check Your MVs

sql SELECT mview_name, refresh_method, last_refresh_date FROM dba_mviews WHERE owner = 'SHOP';

Conclusion

Synonyms shorten names, database links reach other databases and materialized views store results for speed. Pick the refresh method that matches how fresh the data must be.

Quick Summary: Synonym = alias, DB link = remote access, MV = stored query result with refresh.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators