Navigate

SYNONYMS in Oracle

SYNONYMS in Oracle

Synonyms are data base objects which is created to provide a new name to a an object.
The source object can be under the same schema or may belong to another schema.

Purpose of using Synonym

  • Simplify the referring of an object from another schema
  • Shorten a large object name

Mostly used objects for which synonym can be created are:

  • TABLES
  • VIEWS
  • INDEXES
  • SYNONYMS
  • SEQUENCES
  • TYPES
  • CONSTRAINTS
  • PROCEDURES
  • FUNCTIONS
  • PACKAGES
  • TRIGGERS

Types of Synonym

  • PRIVATE SYNONYM
  • PUBLIC SYNONYM

PRIVATE SYNONYM:

Private synonyms are created by individual users.

Creating PRIVATE Synonym:

We have a table called EMPLOYEES_TEST.

SELECT * FROM EMPLOYEES_TEST;
SYNONYMS in Oracle : output
CREATE OR REPLACE SYNONYM SYN_EMP_TEST FOR EMPLOYEES_TEST;
SELECT * FROM SYN_EMP_TEST;
SYNONYMS in Oracle : output
Data Dictionary for PRIVATE Synonym:

We can check the synonym data of current user in USER_SYNONYMS data dictionary view.

SELECT * FROM USER_SYNONYMS
WHERE SYNONYM_NAME = 'SYN_EMP_TEST';
SYNONYMS in Oracle : output
Dropping PRIVATE Synonym:
DROP SYNONYM SYN_EMP_TEST;

Querying Data Dictionary:

SELECT * FROM USER_SYNONYMS
WHERE SYNONYM_NAME = 'SYN_EMP_TEST';
Creating Synonym for object for another schema:
SELECT * FROM SCOTT.EMP;
CREATE OR REPLACE SYNONYM SYN_SCOTT_EMP FOR SCOTT.EMP;

Querying Data Dictionary:

SELECT * FROM USER_SYNONYMS
WHERE SYNONYM_NAME = 'SYN_SCOTT_EMP';
Creating Synonym for large named objects:

We have a function having large name. We want to shorten the function name.
Current function name: FUNCTION_REFERENCE_CURSOR_TEST

SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE 
FROM USER_PROCEDURES
WHERE OBJECT_NAME = 'FUNCTION_REFERENCE_CURSOR_TEST';
CREATE OR REPLACE SYNONYM FUN_REF_CUR_TEST FOR FUNCTION_REFERENCE_CURSOR_TEST;
SELECT * FROM USER_SYNONYMS
WHERE SYNONYM_NAME = 'FUN_REF_CUR_TEST';

PUBLIC SYNONYM:

Public synonyms are created and dropped only by Database Administrator. But they can be accessible to all users.

Creating PUBLIC Synonym:

From SCOTT schema we do not have access on EMPLOYEES table.

CONN SCOTT/TIGER;
SELECT * FROM HR.EMPLOYEES;
CONN SYS/SYS PASSWORD;
CREATE PUBLIC SYNONYM HR_EMP_TAB FOR HR.EMPLOYEES;
GRANT SELECT ON HR_EMP_TAB TO PUBLIC;
CONN SCOTT/TIGER;
SELECT * FROM HR_EMP_TAB;
SYNONYMS in Oracle : output
Data Dictionary for PUBLIC Synonym:

For PUBLIC synonym we will refer DBA_SYNONYMS data dictionary view.

SELECT * FROM DBA_SYNONYMS
WHERE SYNONYM_NAME = 'HR_EMP_TAB';
Dropping PUBLIC Synonym:
CONN SYS/SYS PASSWORD;
DROP PUBLIC SYNONYM HR_EMP_TAB;

Querying Data Dictionary:

SELECT * FROM DBA_SYNONYMS
WHERE SYNONYM_NAME = 'HR_EMP_TAB';
CONN SCOTT/TIGER;
SELECT * FROM HR_EMP_TAB;

RELATED TOPICS:

Exam-Labs Microsoft MS-300

Author

Related Post