🔗 Oracle SQL: CREATE and DROP SYNONYM

🔗 Oracle SQL: CREATE and DROP SYNONYM Explained with Examples

#OracleSQL #CreateSynonym #DropSynonym #SQLPractice #PublicSynonym #PrivateSynonym #SQLLab

In Oracle SQL, a Synonym is an alias or alternative name for a database object like a table, view, sequence, or procedure. Synonyms simplify access to objects and hide the object's schema name.

🛠️ 1. CREATE SYNONYM

Syntax:

CREATE [PUBLIC] SYNONYM synonym_name FOR schema.object_name;

Examples:

-- Create a private synonym
CREATE SYNONYM emp FOR HR.EMPLOYEE;

-- Create a public synonym (requires DBA privilege)
CREATE PUBLIC SYNONYM all_emps FOR HR.EMPLOYEE;

Now, users can access HR.EMPLOYEE simply as emp or all_emps depending on permission scope.

🗑️ 2. DROP SYNONYM

Syntax:

DROP [PUBLIC] SYNONYM synonym_name;

Examples:

-- Drop private synonym
DROP SYNONYM emp;

-- Drop public synonym
DROP PUBLIC SYNONYM all_emps;

This removes the synonym, but not the original object.

🔖 Notes:

• Private synonyms are only accessible by the creator or users with object privileges.
• Public synonyms are available to all users (must be created/dropped by DBA).
• Synonyms help in hiding object ownership, simplifying object access, and improving application portability.

---

🔁 Use synonyms to simplify object names and support cross-schema data access securely.