ExamFinch.com My Study

Study guide

13.3 Synonyms

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

13.3 Synonyms

Key Takeaways Explain This Topic AI

A synonym is an alias or alternative name for a schema object (table, view, sequence, procedure, package, or synonym) stored in the data dictionary that provides location transparency.

Private synonyms exist within a specific user's schema and are accessible only to the owner (or users with schema qualification and grants), whereas Public synonyms belong to the PUBLIC schema and are accessible database-wide.

Oracle resolves unqualified object names through a strict 3-tier hierarchy: Local Schema Object -> Private Synonym -> Public Synonym.

Synonyms provide abstraction and location transparency but do NOT confer object privileges; a user accessing an object via a synonym must still possess direct or role-based grants.

Dropping a synonym does not affect the underlying base object; conversely, dropping the underlying object leaves behind a dangling synonym that raises ORA-00942 at runtime.

Last updated: August 2026

13.3 Synonyms

In enterprise database environments, schema architectures often span multiple user schemas, remote database servers, and evolving object names. Referencing database objects using full schema-qualified names (such as hr.employees or finance.monthly_ledger@ny_sales_link ) introduces tight coupling, security exposure of schema topologies, and extensive code refactoring when objects move.

A Synonym is an alias or alternative pointer for any database schema object. Synonyms store no physical data of their own; they store a metadata definition in the Oracle data dictionary ( USER_SYNONYMS , ALL_SYNONYMS , DBA_SYNONYMS ) that transparently redirects SQL statements to the underlying target object.

Supported Objects and Purposes of Synonyms

Synonyms can be created for virtually any named database object:

  • Tables and Object Tables
  • Views and Materialized Views
  • Sequences
  • Stored PL/SQL Procedures, Functions, and Packages
  • Java Stored Assets and User-Defined Types
  • Other Synonyms (Synonym Chaining)
  • Core Architectural Benefits

Location Transparency: Insulates client applications from schema renames, table migrations, and remote database link ( @dblink ) locations. If a table moves from schema HR to GLOBAL_HR , updating the synonym redirects all applications without modifying a single line of application SQL.

Simplifying SQL Syntax: Eliminates the need for users to prefix object names with schema qualifiers ( hr.employees becomes simply emp ).

Security Abstraction: Obscures the actual schema owner and object names from end users and external client tools.

Private Synonyms vs. Public Synonyms

Oracle Database provides two distinct categories of synonyms: Private Synonyms and Public Synonyms . +-------------------------------------------------------------------------+

  • | PRIVATE VS. PUBLIC SYNONYMS |
  • +-------------------------------------------------------------------------+
  • | |
  • | USER SCHEMA (e.g. 'SCOTT'): DATABASE-WIDE ('PUBLIC' SCHEMA): |
  • | CREATE SYNONYM emp FOR hr.emp; CREATE PUBLIC SYNONYM dept FOR hr.dept;|
  • | - Resides in SCOTT schema - Belongs to PUBLIC schema |
  • | - Owned by SCOTT - Owned by PUBLIC |
  • | - Visible to SCOTT only - Visible to ALL database users |
  • | - Requires CREATE SYNONYM - Requires CREATE PUBLIC SYNONYM |
  • | |
  • +-------------------------------------------------------------------------+
  • Exhaustive Comparison Matrix
  • Architectural Feature Private Synonym Public Synonym
  • Owner / Schema Owned by the creating user schema Owned by the special PUBLIC user group/schema

Scope / Visibility Visible to the owner (or other users via schema.synonym_name ) Globally accessible to all database users

Creation Privilege CREATE SYNONYM (in own schema) or CREATE ANY SYNONYM CREATE PUBLIC SYNONYM (administrative privilege)

Drop Privilege DROP SYNONYM (in own schema) or DROP ANY SYNONYM DROP PUBLIC SYNONYM (administrative privilege)

Namespace Shares the schema namespace with tables, views, sequences, etc. Resides in a separate, global PUBLIC namespace

Primary Use Case Individual developer convenience and local object masking Enterprise-wide shared utility tables, standard views, packages

Synonym Creation Syntax & Clauses

Creating a synonym utilizes the CREATE SYNONYM DDL statement: CREATE [OR REPLACE] [PUBLIC] SYNONYM synonym_name

  • FOR [schema.]object_name[@dblink_name];
  • Syntax Elements Breakdown

OR REPLACE: Re-creates the synonym if it already exists, allowing the target object definition to change without dropping the synonym or invalidating dependent object privileges.

PUBLIC: Creates a public synonym accessible to all users. Omitting PUBLIC creates a private synonym in the current schema.

synonym_name: The identifier assigned to the synonym.

FOR [schema.]object_name: The target object being aliased. If schema is omitted, Oracle assumes the object resides in the current user's schema.

@dblink_name: Specifies a database link to an object residing on a remote Oracle database server.

  • Creation Examples -- 1. Create a private synonym in the current schema pointing to HR.EMPLOYEES
  • CREATE SYNONYM emp_table FOR hr.employees;
  • - 2. Create or replace a public synonym pointing to HR.DEPARTMENTS
  • CREATE OR REPLACE PUBLIC SYNONYM departments FOR hr.departments;
  • - 3. Create a private synonym pointing to a remote table via database link
  • CREATE SYNONYM remote_sales FOR sales.orders@ny_headquarters_db;
  • Oracle 3-Tier Object Name Resolution Hierarchy

When an unqualified identifier (e.g., SELECT * FROM employees; ) appears in a SQL statement, the Oracle SQL parser resolves the object name using a strict 3-Tier Name Resolution Hierarchy . +-------------------------------------------------------------------------+

  • | ORACLE OBJECT NAME RESOLUTION ENGINE |
  • +-------------------------------------------------------------------------+
  • | |
  • | User executes: SELECT * FROM INVOICES; |
  • | |
  • | +-------------------+ |
  • | | TIER 1: LOCAL | |
  • | | SCHEMA OBJECT | |
  • | +---------+---------+ |
  • | | |
  • | +----------------+----------------+ |
  • | | (Found) | (Not Found) |
  • | v v |
  • | [ Access Local Table, +-------------------+ |
  • | View, or Private | TIER 2: PRIVATE | |
  • | Synonym 'INVOICES' ] | SYNONYM (LOCAL) | |
  • | +---------+---------+ |
  • | | |
  • | +----------------+----------------+ |
  • | | (Found) | |
  • | v v |
  • | [ Resolve Private +--------+-|
  • | Synonym Target ] | TIER 3: |
  • | | PUBLIC |
  • | | SYNONYM |
  • | +----+-----+|
  • | | |
  • | +----------------+ |
  • | | (Found) | (No)|
  • | v v |
  • | [ Resolve Public [ ORA-00942: |
  • | Synonym Target ] Table or |
  • | View does |
  • | not exist] |
  • | |
  • +-------------------------------------------------------------------------+
  • Precedence and Shadowing / Masking Rules

Local Objects Always Prevail: If user SCOTT owns a local table named DEPARTMENTS , any query executed by SCOTT referencing DEPARTMENTS will always access SCOTT.DEPARTMENTS , completely ignoring any public synonym named DEPARTMENTS .

Private Synonyms Shadow Public Synonyms: If user SCOTT creates a private synonym named DEPARTMENTS pointing to HR.DEPARTMENTS , SCOTT 's query will access HR.DEPARTMENTS , masking the public synonym.

Public Synonyms Serve as Fallbacks: A public synonym is resolved only if no local object and no private synonym with that name exists in the querying user's schema.

Privilege Decoupling: Synonyms Do NOT Confer Security Grants

A critical conceptual trap on the 1Z0-071 examination is the relationship between synonyms and object privileges.

[!CRITICAL] Synonyms are Pointers, Not Permissions: Creating a synonym (private or public) does NOT grant any privileges on the underlying object. A synonym is purely an alias.

  • Privilege Verification Scenario
  • User ADMIN creates a public synonym: CREATE PUBLIC SYNONYM emp FOR hr.employees;
  • User SCOTT connects and runs: SELECT * FROM emp;

Result: If SCOTT has not been granted SELECT on hr.employees (either directly or via a role), the query fails with: ORA-00942: table or view does not exist (or ORA-01031: insufficient privileges ) .

For SCOTT to query emp , HR or a DBA must execute: GRANT SELECT ON hr.employees TO scott; (or TO PUBLIC ).

Dangling Synonyms and Object Lifecycle Analysis

Oracle does not enforce referential integrity checks between a synonym and its underlying target object at creation time.

1. Creating Synonyms for Non-Existent Objects

You can successfully create a synonym for an object that does not currently exist. Oracle compiles the synonym metadata into the data dictionary without error. -- Table future_inventory does not exist yet!

CREATE SYNONYM inv_syn FOR future_inventory;

-- Synonym created.

-- Attempting to query the synonym fails at runtime:

  • SELECT * FROM inv_syn;
  • - ORA-00942: table or view does not exist
  • 2. The Dangling Synonym Phenomenon

If a base table is dropped, any synonyms pointing to that table are not automatically dropped or updated. They become dangling synonyms . +-------------------------------------------------------------------------+

  • | DANGLING SYNONYM LIFECYCLE |
  • +-------------------------------------------------------------------------+
  • | |
  • | 1. CREATE SYNONYM emp_syn FOR employees; |
  • | - Synonym points to valid EMPLOYEES table |
  • | - Query SELECT * FROM emp_syn succeeds |
  • | |
  • | 2. DROP TABLE employees; |
  • | - EMPLOYEES table is dropped from database |
  • | - EMP_SYN remains in USER_SYNONYMS (Dangling Synonym) |
  • | |
  • | 3. SELECT * FROM emp_syn; |
  • | - Fails with ORA-00942: table or view does not exist |
  • | |
  • | 4. CREATE TABLE employees (...); |
  • | - Table recreated with matching name |
  • | - EMP_SYN immediately works again without re-creation! |
  • | |
  • +-------------------------------------------------------------------------+
  • Dropping Synonyms

Synonyms are dropped using the DROP SYNONYM or DROP PUBLIC SYNONYM statement: -- Drop a private synonym in the current schema

DROP SYNONYM emp_table;

  • - Drop a public synonym (requires DROP PUBLIC SYNONYM privilege)
  • DROP PUBLIC SYNONYM departments;
  • Rules for Dropping Synonyms

Base Object Unaffected: Dropping a synonym deletes only the alias definition from USER_SYNONYMS or ALL_SYNONYMS . It has zero effect on the underlying base table, view, sequence, or stored procedure.

Public Synonym Syntax Requirement: To drop a public synonym, the PUBLIC keyword must be included ( DROP PUBLIC SYNONYM name; ). Executing DROP SYNONYM name; only attempts to drop a private synonym of that name in the user's schema, and fails with ORA-01434: private synonym to be dropped does not exist when no such private synonym exists. ORA-01432: public synonym to be dropped does not exist is the matching error for DROP PUBLIC SYNONYM .

Querying Synonym Metadata in Data Dictionary

Data Dictionary View Scope & Description

USER_SYNONYMS Displays all private synonyms owned by the currently connected user schema.

ALL_SYNONYMS Displays all private synonyms accessible to the user plus all public synonyms.

DBA_SYNONYMS Displays all private and public synonyms across the entire Oracle database instance. SELECT synonym_name, table_owner, table_name, db_link

  • FROM user_synonyms
  • WHERE synonym_name = 'EMP_TABLE';