Oracle – Synonyms

A synonym is an alias for a database object. The object can be a table, view, synonym, sequence, PL/SQL stored procedure, PL/SQL function, PL/SQL package, materialized view.

Synonyms can be created as PRIVATE (by default) or PUBLIC.

  • Private – It exist only in specific user schema.
  • Public – Available to all users in the database.

The name of the object for which you are creating the synonym. It can be one of the following:

  • Table
  • View
  • Sequence
  • Stored Procedure
  • Function
  • Package
  • Materialized View
  • User-defined Object
  • Synonym

You can refer to synonyms in the following statements:

  • Select
  • Insert
  • Update
  • Delete
  • Explain Plan
  • Lock Table

Syntax

Create Synonym (Replace for Altering Synonym)

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

-- Private
-- ========
CREATE OR REPLACE SYNONYM synonym_name FOR object_name;

-- Public
-- =======
CREATE OR REPLACE PUBLIC SYNONYM synonym_name FOR object_name;

-- Public Access On Object For Public Synonym
-- ===========================================
GRANT SELECT ON object_name TO PUBLIC;

Drop Synonym

-- Private
-- ========
DROP SYNONYM synonym_name;

-- Public
-- ========
DROP PUBLIC SYNONYM synonym_name;

Data Dictionary Table

-- Synonyms Owned 
-- ===============
SELECT * FROM USER_SYNONYMS;
SELECT * FROM USER_SYNONYMS WHERE table_name= UPPER('synonym_name');

-- Synonyms Accessible
-- ====================
SELECT * FROM ALL_SYNONYMS;
SELECT * FROM ALL_SYNONYMS WHERE table_name= UPPER('synonym_name');

-- All Synonyms
-- =============
SELECT * FROM DBA_SYNONYMS;
SELECT * FROM DBA_SYNONYMS WHERE table_name= UPPER('synonym_name');

Examples

Prerequisite

DROP TABLE super_heroes_marvel_dc;
CREATE TABLE super_heroes_marvel_dc AS (SELECT employee_id AS id, first_name AS name, salary FROM employees WHERE 1=2);

INSERT INTO emps VALUES(1 , 'Stan Lee'   , 50000);
INSERT INTO emps VALUES(2 , 'Thor'       , 45000);
INSERT INTO emps VALUES(3 , 'Jane Foster', 40000);
INSERT INTO emps VALUES(4 , 'Mary Jane'  , 35000);
INSERT INTO emps VALUES(5 , 'Spider man' , 30000);
INSERT INTO emps VALUES(6 , 'Stark'      , 25000);
INSERT INTO emps VALUES(7 , 'Pepper'     , 20000);
INSERT INTO emps VALUES(8 , 'Oliver'     , 15000);
INSERT INTO emps VALUES(9 , 'Overwatch'  , 10000);
INSERT INTO emps VALUES(10, 'Ethan Hunt' , 5000);

Example 1 – Public

-- Select From Table
-- ==================
SELECT * FROM hr.super_heroes_marvel_dc;

-- Grant Permission to Public
-- ===========================
GRANT SELECT ON hr.super_heroes_marvel_dc TO PUBLIC;

-- Creating Synonym
-- =================
CREATE OR REPLACE PUBLIC SYNONYM heroes FOR hr.super_heroes_marvel_dc;

-- Select From Synonym
-- ====================
SELECT * FROM heroes;

Example 2 – Synonyms for Synonyms

-- Synonym
-- ========
CREATE OR REPLACE SYNONYM heroes_sal FOR super_heroes_marvel_dc;

-- Synonyms for Synonyms
-- ======================
CREATE OR REPLACE SYNONYM heroes FOR heroes_sal;

-- Select From Synonyms for Synonyms
-- ==================================
SELECT * FROM heroes;

Example 3 – DB Link Synonym Usage

-- Creating DB Link
-- =================
CREATE DATABASE LINK my_db_link CONNECT TO user IDENTIFIED BY passwd USING 'alias';

-- Creating Synonym For DB Link Table
-- ===================================
CREATE SYNONYM my_table FOR remote_table@my_db_link;

-- Select From DB Link Table Synonym
-- ==================================
SELECT * FROM my_table;

Reference

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s