41 | CREATE SYNONYMS in Oracle SQL | Oracle PL/SQL Programming

CREATE SYNONYM:
To define the synonym offices for the table locations in the schema hr, issue the following statement:

CREATE SYNONYM offices
FOR hr.locations;

To create a PUBLIC synonym for the employees table in the schema hr on the remote database, you could issue the following statement:

CREATE PUBLIC SYNONYM emp_table
FOR [email protected];

A synonym may have the same name as the underlying object, provided the underlying object is contained in another schema.

Oracle Database Resolution of Synonyms: Example
Oracle Database attempts to resolve references to objects at the schema level before resolving them at the PUBLIC synonym level. For example, the schemas oe and sh both contain tables named customers. In the next example, user SYSTEM creates a PUBLIC synonym named customers for oe.customers:

CREATE PUBLIC SYNONYM customers FOR oe.customers;

If the user sh then issues the following statement, then the database returns the count of rows from sh.customers:

SELECT COUNT(*) FROM customers;

To retrieve the count of rows from oe.customers, the user sh must preface customers with the schema name. (The user sh must have select permission on oe.customers as well.)

SELECT COUNT(*) FROM oe.customers;

If the user hr's schema does not contain an object named customers, and if hr has select permission on oe.customers, then hr can access the customers table in oe's schema by using the public synonym customers:

SELECT COUNT(*) FROM customers;

41 | CREATE SYNONYMS in Oracle SQL | Oracle PL/SQL Programming


#synonyms #oracle_synonym #oracle_table_synonym #oraclesql