Create Synonym in Oracle - Complete Guide with Examples

Create Synonym in Oracle - Complete Guide

Synonyms in Oracle provide alternative names for database objects, simplifying access and providing a layer of abstraction. This guide covers everything you need to know about creating and managing synonyms.

Quick Reference

-- Create private synonym
CREATE SYNONYM emp FOR hr.employees;

-- Create public synonym
CREATE PUBLIC SYNONYM emp FOR hr.employees;

-- Create or replace synonym
CREATE OR REPLACE SYNONYM emp FOR hr.employees;

-- Drop synonym
DROP SYNONYM emp;
DROP PUBLIC SYNONYM emp;

---

Synonym Types

Private Synonyms

Private synonyms are owned by a specific user and only accessible within that schema (unless granted to others).

-- Create private synonym for a table
CREATE SYNONYM orders FOR sales.customer_orders;

-- Create synonym for a view
CREATE SYNONYM active_customers FOR sales.v_active_customers;

-- Create synonym for a sequence
CREATE SYNONYM order_seq FOR sales.order_id_seq;

-- Create synonym for a procedure
CREATE SYNONYM process_order FOR sales.pkg_orders.process_order;

Public Synonyms

Public synonyms are accessible to all database users without qualification.

-- Create public synonym (requires CREATE PUBLIC SYNONYM privilege)
CREATE PUBLIC SYNONYM employees FOR hr.employees;

-- All users can now access:
SELECT * FROM employees;  -- Instead of hr.employees

When to use public synonyms:

• Application tables accessed by multiple schemas
• Utility packages used database-wide
• Hiding schema names from applications

---

Syntax Reference

CREATE SYNONYM

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonym_name
FOR [schema.]object_name[@dblink];

Parameter	Description
OR REPLACE	Replace existing synonym without dropping first
PUBLIC	Create database-wide synonym
schema	Owner schema (default: current user)
synonym_name	Name for the synonym
object_name	Target object (table, view, sequence, etc.)
@dblink	Optional database link for remote objects

Examples

-- Basic table synonym
CREATE SYNONYM emp FOR hr.employees;

-- Synonym with schema qualifier
CREATE SYNONYM scott.emp FOR hr.employees;

-- Remote object synonym (via database link)
CREATE SYNONYM remote_orders FOR orders@production_db;

-- Replace existing synonym
CREATE OR REPLACE SYNONYM emp FOR hr.employees_v2;

---

Required Privileges

For Private Synonyms

-- Grant ability to create synonyms
GRANT CREATE SYNONYM TO username;

-- Grant ability to create any synonym (DBA)
GRANT CREATE ANY SYNONYM TO username;

For Public Synonyms

-- Grant ability to create public synonyms
GRANT CREATE PUBLIC SYNONYM TO username;

-- Grant ability to drop public synonyms
GRANT DROP PUBLIC SYNONYM TO username;

Access to Underlying Object

-- User must have privileges on the base object
GRANT SELECT ON hr.employees TO app_user;

-- Then create synonym
CREATE SYNONYM app_user.emp FOR hr.employees;

---

Common Use Cases

1. Hide Schema Names from Applications

-- Create public synonyms for application tables
CREATE PUBLIC SYNONYM customers FOR app_schema.customers;
CREATE PUBLIC SYNONYM orders FOR app_schema.orders;
CREATE PUBLIC SYNONYM products FOR app_schema.products;

-- Application code uses simple names:
SELECT * FROM customers;  -- Works for any user

2. Simplify Cross-Schema Access

-- Without synonym
SELECT * FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id;

-- Create synonyms
CREATE SYNONYM emp FOR hr.employees;
CREATE SYNONYM dept FOR hr.departments;

-- With synonyms (cleaner)
SELECT * FROM emp e
JOIN dept d ON e.department_id = d.department_id;

3. Database Link Abstraction

-- Create synonym for remote table
CREATE SYNONYM remote_inventory FOR inventory@warehouse_db;

-- Local queries work seamlessly
SELECT * FROM remote_inventory WHERE quantity < 10;

4. Version Migration

-- Point synonym to new table version
CREATE OR REPLACE SYNONYM products FOR schema.products_v2;

-- All existing code continues to work
-- Rollback is easy:
CREATE OR REPLACE SYNONYM products FOR schema.products_v1;

---

Managing Synonyms

View Existing Synonyms

-- All synonyms owned by current user
SELECT synonym_name, table_owner, table_name, db_link
FROM user_synonyms
ORDER BY synonym_name;

-- All synonyms accessible to current user
SELECT owner, synonym_name, table_owner, table_name
FROM all_synonyms
WHERE table_owner = 'HR'
ORDER BY synonym_name;

-- All public synonyms
SELECT synonym_name, table_owner, table_name
FROM dba_synonyms
WHERE owner = 'PUBLIC'
ORDER BY synonym_name;

Drop Synonyms

-- Drop private synonym
DROP SYNONYM synonym_name;

-- Drop public synonym
DROP PUBLIC SYNONYM synonym_name;

-- Generate drop statements for all user synonyms
SELECT 'DROP SYNONYM ' || synonym_name || ';'
FROM user_synonyms;

Find Invalid Synonyms

-- Synonyms pointing to non-existent objects
SELECT s.owner, s.synonym_name, s.table_owner, s.table_name
FROM dba_synonyms s
WHERE NOT EXISTS (
  SELECT 1 FROM dba_objects o
  WHERE o.owner = s.table_owner
    AND o.object_name = s.table_name
);

---

Best Practices

Do’s

1. Use meaningful names - Synonyms should be intuitive
2. Document public synonyms - Track what they point to
3. Use OR REPLACE - Prevents “name already exists” errors
4. Grant minimum privileges - Only CREATE SYNONYM, not CREATE ANY

Don’ts

1. Don’t create circular synonyms - Synonym A pointing to Synonym B pointing to A
2. Don’t use synonyms for temporary objects - Creates confusion
3. Don’t create public synonyms for sensitive data - Security risk
4. Avoid synonym chains - Synonym to synonym to object

---

Troubleshooting

ORA-00955: Name Already Used

-- Check what's using the name
SELECT object_type, owner, object_name
FROM dba_objects
WHERE object_name = UPPER('&name');

-- Use OR REPLACE to overwrite
CREATE OR REPLACE SYNONYM emp FOR hr.employees;

ORA-01775: Looping Chain of Synonyms

-- Find the chain
SELECT * FROM dba_synonyms
WHERE synonym_name = 'PROBLEM_SYNONYM'
   OR table_name = 'PROBLEM_SYNONYM';

-- Fix by dropping and recreating properly
DROP SYNONYM problem_synonym;
CREATE SYNONYM problem_synonym FOR actual_schema.actual_table;

ORA-00980: Synonym Translation No Longer Valid

-- Underlying object was dropped
-- Find what synonym points to:
SELECT table_owner, table_name
FROM user_synonyms
WHERE synonym_name = 'BROKEN_SYNONYM';

-- Either recreate the object or drop the synonym
DROP SYNONYM broken_synonym;

---

Related Topics

• ORA-00955: Name Already Used - Object naming conflicts
• Database Links - Remote database access
• Security Scripts - Privilege management