This tsection explores Oracle 19c synonyms, a powerful feature for simplifying object names and enhancing database flexibility. Learn how to CREATE SYNONYM, DROP SYNONYM, and understand the distinction between private and public synonyms for improved SQL access and security.

Synonyms: Simplifying Object Names

Synonyms are alternative names for database objects such as tables, views, sequences, or stored procedures. They provide a layer of abstraction, simplifying SQL statements and making applications more flexible by hiding the actual object's name and location.

CREATE SYNONYM

The CREATE SYNONYM statement is used to define a new synonym for a database object. This allows users to refer to the object using a simpler or different name without specifying its schema or database link.

Example 1: Creating a Private Synonym

-- Connect as a user (e.g., HR) who owns the employees table
-- Then, connect as another user (e.g., SCOTT) to create the synonym for HR.employees
CREATE SYNONYM emp
FOR hr.employees; -- Creates a private synonym 'emp' for the 'employees' table owned by 'hr'

Explanation This code creates a private synonym named emp for the employees table owned by the HR schema. Only the user who created it (or users granted access) can use this synonym.

Example 2: Using a Private Synonym

-- Assuming SCOTT created the 'emp' synonym
SELECT employee_id, first_name
FROM emp
WHERE ROWNUM <= 5; -- Selects data from the employees table using the 'emp' synonym

Explanation This SQL query retrieves data from the employees table by referencing its private synonym emp, demonstrating simplified access.

Example 3: Creating a Public Synonym

-- Connect as a privileged user (e.g., SYS or SYSTEM)
CREATE PUBLIC SYNONYM all_emps
FOR hr.employees; -- Creates a public synonym 'all_emps' for 'hr.employees', accessible by all users

Explanation This code creates a public synonym all_emps for hr.employees, making the employees table accessible to all database users without schema qualification.

Example 4: Using a Public Synonym

-- Any user can execute this after a public synonym is created
SELECT employee_id, first_name
FROM all_emps
WHERE ROWNUM <= 5; -- Selects data from employees table using the public synonym 'all_emps'

Explanation This query allows any database user to access the employees table via the all_emps public synonym, streamlining access.

Example 5: Synonym for a View

CREATE SYNONYM active_emp_view
FOR hr.active_employees_v; -- Creates a synonym for a view named 'active_employees_v'

Explanation This example demonstrates creating a synonym for a database view, providing an alternative, shorter name for the view.

DROP SYNONYM

The DROP SYNONYM statement is used to remove an existing synonym from the database. This effectively eliminates the alternative name, requiring users to refer to the original object directly.

Example 1: Dropping a Private Synonym

-- Connect as the user who created the private synonym (e.g., SCOTT)
DROP SYNONYM emp; -- Removes the private synonym 'emp'

Explanation This code statement drops the private synonym emp, meaning the user SCOTT can no longer refer to hr.employees using emp.

Example 2: Dropping a Public Synonym

-- Connect as a privileged user (e.g., SYS or SYSTEM)
DROP PUBLIC SYNONYM all_emps; -- Removes the public synonym 'all_emps'

Explanation This code drops the public synonym all_emps, revoking the simplified access to hr.employees for all database users.

Example 3: Attempting to Drop Non-existent Synonym

-- This will result in an error if 'non_existent_syn' does not exist
DROP SYNONYM non_existent_syn; -- Attempts to drop a synonym that does not exist

Explanation This example shows that attempting to drop a synonym that does not exist will result in an ORA-01432 error.

Example 4: Dropping a Synonym with IF EXISTS (Oracle 12c+)

-- This syntax is available from Oracle 12c onwards to prevent errors
DROP SYNONYM IF EXISTS emp; -- Drops 'emp' only if it exists, preventing an error

Explanation This code uses the IF EXISTS clause (available from Oracle 12c and higher) to prevent an error if the synonym emp does not exist.

Example 5: Dropping a Synonym for a Dropped Object

-- Assume 'old_table' was dropped, but its synonym 'ot' still exists
-- This will successfully drop the synonym, even if the underlying object is gone.
DROP SYNONYM ot; -- Drops a synonym whose underlying object might have been dropped

Explanation This demonstrates that a synonym can be dropped even if the database object it points to no longer exists.

Private vs. Public Synonyms

Oracle offers two types of synonyms: private and public. Private synonyms are created by a specific user and are only accessible by that user, while public synonyms are created by privileged users and are accessible by all users in the database.

Example 1: Creating a Private Synonym (User Context)

-- User 'APP_USER' creates a private synonym for a table in 'DATA_OWNER' schema
CREATE SYNONYM cust_data
FOR data_owner.customers; -- 'cust_data' is only visible and usable by APP_USER

Explanation This code shows APP_USER creating a private synonym cust_data, which simplifies access to data_owner.customers exclusively for APP_USER.

Example 2: Listing Private Synonyms

SELECT synonym_name, table_owner, table_name
FROM user_synonyms; -- Lists all private synonyms owned by the current user

Explanation This query retrieves information about all private synonyms created by the currently logged-in user from the user_synonyms data dictionary view.

Example 3: Creating a Public Synonym (Admin Context)

-- CONNECT AS SYSDBA OR SYSTEM
CREATE PUBLIC SYNONYM global_products
FOR product_schema.products; -- 'global_products' is accessible to ALL database users

Explanation This code illustrates an administrator creating a public synonym global_products, making product_schema.products accessible to every user in the database.

Example 4: Listing Public Synonyms

SELECT synonym_name, table_owner, table_name
FROM all_synonyms
WHERE owner = 'PUBLIC'; -- Lists all public synonyms in the database

Explanation This query retrieves details of all public synonyms by querying all_synonyms where the owner is 'PUBLIC'.

Example 5: Ambiguity Resolution (Private vs. Public)

-- If both a private synonym 'my_table' and a public synonym 'my_table' exist
-- Oracle resolves to the private synonym first within the user's session.
-- Example scenario:
-- User SCOTT creates PRIVATE SYNONYM MY_TABLE FOR SCOTT.MY_ACTUAL_TABLE;
-- SYS creates PUBLIC SYNONYM MY_TABLE FOR HR.OTHER_TABLE;
-- When SCOTT runs SELECT * FROM MY_TABLE;, it refers to SCOTT.MY_ACTUAL_TABLE.

Explanation When both private and public synonyms with the same name exist, Oracle prioritizes the private synonym within the current user's schema, ensuring predictable object resolution.