Oracle SQL Certification 1Z0-071 Course | Section 17: Synonyms

Synonyms are a simple yet powerful feature in Oracle that help you simplify object names, improve accessibility, and enhance flexibility when working with database objects.

Instead of repeatedly typing long schema-qualified names, you can create a short, meaningful alias and use it just like the original object.

Lesson 1: Synonyms Overview

A synonym is an alternative name (alias) for another database object.

It can reference:

  • Tables
  • Views
  • Sequences
  • Other database objects
Why Use Synonyms?

Synonyms are extremely useful for:

  • Simplifying long object names
  • Hiding schema ownership details
  • Providing abstraction for database objects
  • Supporting backward compatibility
  • Enabling cross-schema access
Example Without Synonym
SELECT * FROM hr.employees;
Example With Synonym
SELECT * FROM employees;

Here, employees is a synonym pointing to hr.employees.

Key Benefits
  • Cleaner and more readable SQL
  • Easier maintenance when object names change
  • Flexibility when working across schemas

Lesson 2: Creating and Managing Synonyms

Oracle allows you to create synonyms using the CREATE SYNONYM statement.

Creating a Private Synonym

A private synonym is available only within your schema:

CREATE SYNONYM emp FOR hr.employees;

Now you can query:

SELECT * FROM emp;
Creating a Public Synonym

A public synonym is accessible to all users:

CREATE PUBLIC SYNONYM emp FOR hr.employees;

Note: Creating public synonyms typically requires special privileges.

Using OR REPLACE

To redefine an existing synonym:

CREATE OR REPLACE SYNONYM emp FOR hr.new_employees;

This updates the synonym without dropping it first.

Using Synonyms in DML

You can use synonyms just like the original object:

INSERT INTO emp (employee_id, name)
VALUES (101, 'John Doe');
Dropping Synonyms

Private synonym:

DROP SYNONYM emp;

Public synonym:

DROP PUBLIC SYNONYM emp;
Public vs Private Synonyms
FeaturePrivate SynonymPublic Synonym
ScopeSingle schemaEntire database
AccessibilityLimitedGlobal
PrivilegesStandardRequires elevated rights

Final Thoughts

Synonyms are a lightweight but powerful feature that improves:

  • Readability → shorter, cleaner queries
  • Flexibility → easy object replacement
  • Security abstraction → hide schema details
  • Cross-schema usability → seamless access

For the Oracle 1Z0-071 exam, understanding synonyms is important, especially the difference between public and private synonyms, and how they behave.

In real-world databases, synonyms are widely used to create a clean abstraction layer, making systems easier to maintain and scale.