If you work with multiple schemas or long object names, synonyms in Oracle Database can make your SQL cleaner and your apps easier to maintain. A synonym is a friendly alias for another database object, such as a table, view, sequence, package, or even a remote object over a database link.
You write shorter SQL, you decouple your code from schema names, and you reduce breakage when things move.
In this tutorial, you will learn what Oracle synonyms are, when to use them, how to create them, and a few gotchas to avoid.
What are synonyms in Oracle Database?
A synonym is a name that points to another database object. Instead of referencing HR.TUTORIAL_PRODUCTS everywhere, you can create a synonym called PRODUCTS and use that name in your queries.
The underlying object does not change. The synonym only forwards your reference to the right place.
Why it matters:
- Simpler SQL, fewer schema prefixes in your code.
- Decoupling, you can move or rename underlying objects and keep the same synonym.
- Security layering, you can expose a synonym while hiding the base schema.
Synonyms do not grant privileges. You still need proper GRANTs on the target object.
Private synonyms vs public synonyms
- Private synonym, defined in a specific schema, visible only to that schema. Good for application users and clear ownership.
- Public synonym, defined once and visible to all users who have privileges on the target. Handy for shared utilities; risky if overused due to name collisions.
You need the right privileges to create them. Typically, CREATE SYNONYM for private synonyms, CREATE PUBLIC SYNONYM for public synonyms.
Prepare a tiny dataset
We will create a small table in HR, then grant access to APPUSER.
-- In HR schema CREATE TABLE hr.tutorial_products ( id NUMBER PRIMARY KEY, name VARCHAR2(50) ); INSERT INTO hr.tutorial_products (id, name) VALUES (1, 'Laptop'); INSERT INTO hr.tutorial_products (id, name) VALUES (2, 'Mouse'); COMMIT; -- Allow APPUSER to use the table GRANT SELECT, INSERT, UPDATE, DELETE ON hr.tutorial_products TO appuser;
Create and use a private synonym
As APPUSER, create a synonym that points to HR.TUTORIAL_PRODUCTS.
-- In APPUSER schema CREATE SYNONYM products FOR hr.tutorial_products; -- Use it like a normal table SELECT * FROM products;
Focus on this part of the code:
... FOR hr.tutorial_products;
The FOR clause tells Oracle which object the synonym maps to. The left side is the synonym name, the right side is the fully qualified target. When you run:
SELECT * FROM products;
Oracle resolves PRODUCTS to HR.TUTORIAL_PRODUCTS, then checks your GRANTs.
If you later move the table to another schema, you can update the synonym instead of changing all your queries.
Create a public synonym
Public synonyms are shared. Only a DBA or a user with the right system privilege can create one.
-- As a DBA or user with CREATE PUBLIC SYNONYM CREATE PUBLIC SYNONYM hr_products FOR hr.tutorial_products;
In this statement, the keyword PUBLIC makes the synonym available to everyone. The target is still controlled by privileges. If a user lacks SELECT on HR.TUTORIAL_PRODUCTS, this query fails even though the synonym exists:
SELECT * FROM hr_products;
Best practice from my experience, prefer private synonyms for application schemas, keep public synonyms limited and well documented.
Replace and drop synonyms, plus name resolution
You can update an existing synonym without dropping it first.
CREATE OR REPLACE SYNONYM products FOR hr.tutorial_products;
The OR REPLACE part tells Oracle to overwrite the previous definition. Dropping is straightforward:
DROP SYNONYM products; DROP PUBLIC SYNONYM hr_products;
Name resolution rules matter. If you create a local object with the same name as your synonym, Oracle resolves the local object first.
-- In APPUSER CREATE TABLE appuser.products (id NUMBER); -- This selects from APPUSER.PRODUCTS table, not the synonym SELECT * FROM products;
Two common gotchas:
- Synonyms do not grant access. You still need GRANT SELECT, INSERT, UPDATE, and DELETE on the base object.
- If the target object is dropped or changed, you may see “synonym translation is no longer valid.” Recreate the synonym or fix the target.
Synonyms for sequences and remote objects
Synonyms work for more than tables.
-- Sequence CREATE SYNONYM order_seq FOR hr.order_seq; SELECT order_seq.NEXTVAL FROM dual;
Here, NEXTVAL runs on the sequence that the synonym points to.
-- Remote table over a database link CREATE SYNONYM remote_orders FOR orders@sales_dblink; SELECT * FROM remote_orders;
The @sales_dblink part routes the call to the remote database. The synonym hides the link name from your code.
Find and audit your synonyms
Use data dictionary views to list or troubleshoot synonyms.
SELECT owner, synonym_name, table_owner, table_name, db_link FROM all_synonyms WHERE synonym_name = 'PRODUCTS';
USER_SYNONYMS shows synonyms you own. ALL_SYNONYMS shows synonyms you can access. DBA_SYNONYMS shows all synonyms.
Wrap up
Synonyms in Oracle Database give you clean names, flexible deployment, and simpler SQL. Use private synonyms to keep app code tidy and decoupled from schema details. Use public synonyms only when you truly need a shared entry point.
Always remember that privileges live on the base object, not the synonym. Keep names consistent, avoid chains of synonyms, and check the dictionary views when you need to debug.
