A synonym is an identifier that can be used to reference another database object in a SQL statement.

Syntax

Use the CREATE SYNONYM statement to create a synonym. The syntax is as follows:

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

To delete a synonym, use the DROP SYNONYM statement. The syntax is as follows:

DROP [PUBLIC] SYNONYM [schema.] syn_name;

Parameters

Parameter Description
syn_name The name of the synonym. The name of a synonym must be unique within a schema.
schema The name of the schema where the synonym resides.
Note If you do not specify a schema name, the synonym is created in the first existing schema in your search path.
object_name The name of the object.
object_schema The name of the schema where the object resides.

Description

A synonym is an identifier that can be used to reference another database object in a SQL statement.

A synonym is useful in cases where a database object normally requires full qualification by schema name to be properly referenced in a SQL statement. A synonym defined for that object simplifies the reference to a single, unqualified name.

PolarDB for Oracle supports synonyms for:
  • Tables
  • Views
  • Sequences
  • Procedures
  • Functions
  • Types
  • Other synonyms

Neither the referenced schema nor referenced object must exist at the time that you create the synonym. A synonym may refer to a non-existent object or schema. A synonym will become invalid if you drop the referenced object or schema. You must explicitly drop a synonym to remove it.

As with any other schema object, PolarDB for Oracle uses the search path to resolve unqualified synonym names. If you have two synonyms that have the same name, an unqualified reference to a synonym will resolve to the first synonym that has the specified name in the search path. If public is in your search path, you can refer to a synonym in that schema without qualifying that name.

When PolarDB for Oracle executes a SQL statement, the privileges of the current user are checked against the underlying database object of the synonym. If the user does not have the proper permissions for that object, the SQL statement will fail.

Examples

  • Create a synonym

    Include the REPLACE clause to replace an existing synonym definition with a new synonym definition.

    Include the PUBLIC clause to create the synonym in the public schema. Compatible with Oracle databases, the CREATE PUBLIC SYNONYM statement creates a synonym that resides in the public schema:

    CREATE [OR REPLACE] PUBLIC SYNONYM syn_name FOR object schema.object name;

    The following statement is a shorthand way to write:

    CREATE [OR REPLACE] SYNONYM public.syn_name FOR object schema.object name;

    The following example creates a synonym named personnel that refers to the polardb.emp table.

    CREATE SYNONYM personnel FOR polardb.emp;

    Unless the synonym is schema qualified in the CREATE SYNONYM statement, the synonym will be created in the first existing schema in your search path. You can view your search path by executing the following statement:

    SHOW SEARCH_PATH;
    
          search_path
    -----------------------
     development,accounting
    (1 row)            

    In the example, if a schema named development does not exist, the synonym will be created in the schema named accounting.

    The emp table in the polardb schema can be referenced in any DDL or DML statement, by using the personnel synonym:

    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)
  • Delete a synonym

    Like any other object that can be schema-qualified, you may have two synonyms with the same name in your search path. To disambiguate the name of the synonym that you are dropping, include a schema name. Unless a synonym is schema qualified in the DROP SYNONYM statement, PolarDB for Oracle deletes the first instance of the synonym it finds in your search path.

    You can optionally include the PUBLIC clause to drop a synonym that resides in the public schema. Compatible with Oracle databases, the DROP PUBLIC SYNONYM statement drops a synonym that resides in the public schema:

    DROP PUBLIC SYNONYM syn_name;

    The following example drops the personnel synonym:

    DROP SYNONYM personnel;