Oracle Database Synonyms: From Basics to Practical Use Explained Simply

Introduction

“I want to remove schema names from my application SQL.”
“I want to switch the referenced table depending on the environment.”
“I don’t want to write long references through a DB link.”

In such cases, Synonyms are very useful.

This article explains synonyms from mechanism and types to creation methods, integration with DB links, operational considerations, and how to deal with common errors. Everything is organized so you can apply it directly in practice.
The sample SQL covers user creation, privilege grants, table creation, and INSERT statements.

What is a Synonym?

A Synonym is an alias for objects such as tables, views, sequences, or PL/SQL objects.

It hides long object names or schema qualifications, improving SQL readability and portability. Note that privileges cannot be granted to a synonym itself—you must have privileges on the underlying object.

Conceptual Diagram

(Actual) SCOTT.EMP  ──────┐
                          │   ← Alias (Synonym)
(Alias) EMP_SYN     ──────┘

Types: Private and Public

Private Synonym: Usable only by the user who created it (within the same schema).
Example: CREATE SYNONYM emp_syn FOR scott.emp;
Public Synonym: Usable by all users in the database. However, each user still needs privileges on the underlying object. Creating public synonyms requires administrative privileges.

Resolution Order (Important)

When resolving object names, Oracle generally checks in this order:
Own schema → Private synonym → Public synonym.
To avoid conflicts, careful naming design is important.

Resolution Order Diagram

┌──────────────┐
│ 1. Own Schema │  Example: EMP (own table)
├──────────────┤
│ 2. Private    │  Example: EMP (alias) = SCOTT.EMP
├──────────────┤
│ 3. Public     │  Example: EMP (alias) = SCOTT.EMP
└──────────────┘

Basic Syntax for Creation and Management

Create: CREATE [OR REPLACE] [PUBLIC] SYNONYM alias FOR schema.object[@DBLINK];
Drop: DROP [PUBLIC] SYNONYM alias;

Required Privileges

Private synonym in own schema: CREATE SYNONYM
Private synonym in another schema: CREATE ANY SYNONYM
Public synonym: CREATE PUBLIC SYNONYM (for administrators)

(Regardless, SELECT or other privileges must be granted on the underlying object.)

DB Link × Synonym: Shortening References

With DB links, long references like SCOTT.EMP@REMOTE_DB_LINK can be shortened with synonyms such as EMP_REMOTE.
This allows application SQL to remain consistent across environments.

[Local DB]                   DB Link                [Remote DB]
┌──────────┐        ───────────────▶        ┌──────────┐
│ APP User │                               │ SCOTT.EMP │
└──────┬───┘                               └──────────┘
       │ (Alias)
       └─ EMP_REMOTE (Synonym)

Checking Synonyms and Dependencies

Lists: USER_SYNONYMS, ALL_SYNONYMS, DBA_SYNONYMS
Dependencies: USER_DEPENDENCIES (useful to check referenced objects)

Usage Patterns

Schema hiding (simplifying application SQL)
Application can keep SELECT * FROM CUSTOMER; while changing the synonym definition per environment.
Environment switching (production/test)
Reuse the same SQL by swapping only the referenced object.
Shortening DB link references
Hide destination or network details from the application.

Pros and Cons

Advantages

Improves readability and maintainability of SQL (hides schema/DB link)
Absorbs environmental differences (easy to switch references)
Minimizes application changes when object names change

Disadvantages / Cautions

Risk of name conflicts (understand resolution order and enforce naming conventions)
Overuse of public synonyms pollutes the global namespace and causes confusion (private synonyms are recommended)
Privileges must be managed separately (cannot be granted to synonyms directly)

Execution Example (Including User Creation, Privileges, Tables, INSERT)

Assumption: Connected to a PDB (use ALTER SESSION SET CONTAINER=... if necessary).
We will create two users: OWNER1 (object owner) and APP1 (application user).

1) Create Users and Grant Privileges (as DBA)

-- Owner user
CREATE USER owner1 IDENTIFIED BY "Owner1#Pass"
  DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO owner1;

-- Application user
CREATE USER app1 IDENTIFIED BY "App1#Pass"
  DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO app1;

-- Synonym creation privilege (private)
GRANT CREATE SYNONYM TO app1;

-- For public synonym tests, grant only as admin if necessary
-- GRANT CREATE PUBLIC SYNONYM TO SYSTEM;

2) Create Tables and Insert Sample Data (as OWNER1)

CONN owner1/"Owner1#Pass"

CREATE TABLE dept(
  deptno NUMBER(2)   PRIMARY KEY,
  dname  VARCHAR2(14),
  loc    VARCHAR2(13)
);

CREATE TABLE emp(
  empno    NUMBER(4)   PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)   REFERENCES dept(deptno)
);

INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');

INSERT INTO emp(empno,ename,job,deptno,hiredate,sal)
VALUES (7369,'SMITH','CLERK',20,DATE '1980-12-17',800);
INSERT INTO emp(empno,ename,job,deptno,hiredate,sal)
VALUES (7566,'JONES','MANAGER',20,DATE '1981-04-02',2975);
COMMIT;

3) Grant Access Privileges (OWNER1 → APP1)

GRANT SELECT ON owner1.emp  TO app1;
GRANT SELECT ON owner1.dept TO app1;

4) Create and Use Private Synonyms (as APP1)

CONN app1/"App1#Pass"

-- Create aliases in own schema
CREATE SYNONYM emp_syn  FOR owner1.emp;
CREATE SYNONYM dept_syn FOR owner1.dept;

-- Now you can reference without schema qualification
SELECT empno, ename, job FROM emp_syn ORDER BY empno;
SELECT deptno, dname     FROM dept_syn ORDER BY deptno;

Execution Example:
(unchanged from original, showing error messages and outputs)
[Original SQL outputs preserved]

SQL> show user
USER is "APP1"
SQL> SELECT empno, ename, job FROM emp ORDER BY empno;
SELECT empno, ename, job FROM emp ORDER BY empno
                              *
ERROR at line 1:
ORA-00942: table or view does not exist

  --★Since it is a table in another schema, you cannot select it unless you specify the schema name.


SQL> SELECT empno, ename, job FROM owner1.emp ORDER BY empno;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7566 JONES      MANAGER

  --★You can select by adding a schema name

SQL> SELECT deptno, dname     FROM dept ORDER BY deptno;
SELECT deptno, dname     FROM dept ORDER BY deptno
                              *
ERROR at line 1:
ORA-00942: table or view does not exist

  --★Since it is a table in another schema, you cannot select it unless you specify the schema name.


SQL> SELECT deptno, dname     FROM owner1.dept ORDER BY deptno;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES

  --★You can select by adding a schema name

SQL> SELECT empno, ename, job FROM emp_syn ORDER BY empno;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7566 JONES      MANAGER

SQL> SELECT deptno, dname     FROM dept_syn ORDER BY deptno;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES

  --★Select using synonyms

5) Create a Public Synonym (DBA only, with caution)

CREATE PUBLIC SYNONYM dept FOR owner1.dept;

Even though it looks available to all users, each user still requires privileges on owner1.dept.

6) Synonym with DB Link (Advanced)

Example: Referencing SCOTT.EMP on a remote DB from APP1.

CONN app1/"App1#Pass"

CREATE DATABASE LINK remote_db_link
  CONNECT TO scott IDENTIFIED BY tiger
  USING 'REMOTE_SVC';

CREATE SYNONYM emp_remote FOR scott.emp@remote_db_link;

SELECT empno, ename FROM emp_remote WHERE job = 'MANAGER';

(For details on DBLINK, see the related article.)

7) Managing Synonyms (Replace / Drop)

-- Redefine to point elsewhere
CREATE OR REPLACE SYNONYM emp_syn FOR owner1.emp_archive;

-- Drop
DROP SYNONYM emp_syn;

-- For public synonyms
-- DROP PUBLIC SYNONYM dept;

FAQs (Troubleshooting)

Q1. ORA-00980: Synonym translation is invalid.
A. Occurs when the referenced object does not exist or privileges are missing.
→ Confirm existence of the target, grant privileges, check DB link connectivity, or recreate with CREATE OR REPLACE.

Q2. ORA-01775: Synonym definition is a loop.
A. Synonyms point to each other (or to themselves), creating a loop.
→ Recreate to point to the correct object or drop the unnecessary synonym.

Q3. Can privileges be granted to synonyms?
A. No. Privileges must be granted to the underlying object.

Q4. Which to choose: public or private?
A. Prefer private synonyms. Public synonyms carry risks of namespace pollution and wider unintended impact. Use them only when necessary.

Q5. Where to confirm resolution order?
A. As documented: own schema → private → public.

Summary (Key Points)

A synonym is an alias; privileges are managed on the actual object.
Two types: private and public (private is generally recommended).
Understand the resolution order and avoid conflicts via naming conventions.
Combine synonyms with DB links to shorten references and simplify environment switching.
Regularly check USER/ALL/DBA_SYNONYMS and dependencies.
Common errors (ORA-00980, ORA-01775) can be solved by reviewing references and design.

Reference

SQL Language Reference – CREATE SYNONYM