SQL Synonyms | Synonyms | Private Synonym | Public Synonym | SQL | Oracle SQL

 

Synonyms

Creating and Dropping Synonyms

•       Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects.

Syntax:

                CREATE [PUBLIC] SYNONYM synonymname

                FOR object;

In the syntax:

                  PUBLIC     creates a synonym accessible to all users.

                Synonym    is the name of the synonym to be created.

                    Object     identifies the object for which the synonym is created.

Examples:      1) CREATE SYNONYM XX_EMP_EMPID_SYN

                             FOR XX_EMP_EMPID;

                         2) DROP SYNONYM XX_EMP_EMPID;

Private Synonyms:

• Private synonyms are owned by a specific user.
• They are only accessible to the user who created them.
• To create a private synonym:

Syntax:

CREATE SYNONYM synonym_name FOR schema_name.object_name;

Public Synonyms:

• Public synonyms are accessible to all users in the database.
• They are typically created by a privileged user and can be used by any other user.
• To create a public synonym:

Syntax:

CREATE PUBLIC SYNONYM synonym_name FOR schema_name.object_name;