Create and drop a synonym - PolarDB

A synonym is an alias for a database object. Instead of qualifying every reference with a full schema name — such as polardb.emp — define a synonym once and use the short name across all DML and DDL statements.

PolarDB for Oracle supports synonyms for the following object types:

Tables
Views
Sequences
Procedures
Functions
Types
Other synonyms

How synonyms work

A synonym maps a single, unqualified name to a target object in another schema (or database, via a database link). When PolarDB for Oracle resolves a SQL statement that references a synonym, it transparently substitutes the underlying object.

Key behaviors:

Deferred binding: The target object and schema do not have to exist when you create the synonym. The synonym becomes invalid only when you drop the referenced object or schema.
Explicit removal: Dropping the target object does not drop the synonym. Remove the synonym explicitly with DROP SYNONYM.
Privilege check: A synonym is not a substitute for object privileges. When a user runs a statement that references a synonym, PolarDB for Oracle checks that user's privileges against the underlying object, not against the synonym itself.
Name resolution: Unqualified synonym names are resolved using the search path. If two synonyms share the same name, the first match in the search path takes precedence. If public is in the search path, public synonyms are accessible without schema qualification.

Syntax

CREATE SYNONYM

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]syn_name
    FOR object_schema.object_name[@dblink_name];

Use OR REPLACE to overwrite an existing synonym definition without dropping it first.

Use PUBLIC to create the synonym in the public schema, making it accessible without schema qualification to any user whose search path includes public. The following two statements are equivalent:

CREATE [OR REPLACE] PUBLIC SYNONYM syn_name FOR object_schema.object_name;
CREATE [OR REPLACE] SYNONYM public.syn_name FOR object_schema.object_name;

DROP SYNONYM

DROP [PUBLIC] SYNONYM [schema.]syn_name;

Use PUBLIC to drop a synonym that resides in the public schema:

DROP PUBLIC SYNONYM syn_name;

Parameters

Parameter	Description
`syn_name`	The name of the synonym. Must be unique within a schema.
`schema`	The schema where the synonym resides. If omitted, PolarDB for Oracle creates the synonym in the first existing schema found in the search path.
`object_name`	The name of the target object.
`object_schema`	The schema where the target object resides.

Examples

Create a synonym

Create a synonym named personnel for the polardb.emp table:

CREATE SYNONYM personnel FOR polardb.emp;

Because no schema is specified, PolarDB for Oracle creates the synonym in the first existing schema in the search path. To check your current search path:

SHOW SEARCH_PATH;

      search_path
-----------------------
 development,accounting
(1 row)

If the development schema does not exist, the synonym is created in the accounting schema.

Use a synonym in DML statements

After creating the synonym, use it in any DML statement in place of the fully qualified table name:

INSERT INTO personnel VALUES (8142,'ANDERSON','CLERK',7902,'17-DEC-06',1300,NULL,20);

SELECT * FROM personnel;

 empno |  ename   |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+----------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH    | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7499 | ALLEN    | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD     | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES    | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN   | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE    | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK    | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT    | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING     | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER   | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS    | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES    | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD     | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER   | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
  8142 | ANDERSON | CLERK     | 7902 | 17-DEC-06 00:00:00 | 1300.00 |         |     20
(15 rows)

Drop a synonym

Drop the personnel synonym:

DROP SYNONYM personnel;

If two synonyms have the same name in the search path, include the schema name to target the correct one:

DROP SYNONYM accounting.personnel;