Create a synonym|V4.0.0|OceanBase Database| docs|Distributed Database

Create a synonym

Last Updated：2023-07-24 09:52:12 Updated

You can use the CREATE SYNONYM statement to create a synonym. This topic describes how to create a synonym.

Prerequisites

To create a private synonym in your schema, you must have the CREATE SYNONYM system privilege.
To create a private synonym in the schema of another user, you must have the CREATE ANY SYNONYM system privilege.
To create a public synonym, you must have the CREATE PUBLIC SYNONYM system privilege.

Syntax

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

Parameters

Parameter	Description
OR REPLACE	Re-creates a synonym if it already exists.
PUBLIC	Specifies to create a public synonym, which can be used by all users. Only users with the corresponding privileges on the underlying object can use this synonym. A public synonym is used in parsing the reference to an object only when no schema is specified for the object. If `PUBLIC` is omitted, the synonym is private. A private synonym name must be unique in its schema and can be accessed only by using its schema.
[schema. ]synonym	Specifies the schema to which the synonym to be created belongs. If you have specified `PUBLIC`, you cannot specify the schema for the synonym. The `synonym` field specifies the name of the synonym.
[schema. ]object	Specifies the name of the object that the synonym corresponds to.

Note

You can check the synonyms that you have created in the USER_SYNONYMS, ALL_SYNONYMS, and DBA_SYNONYMS views.

Examples

Example 1

To create a private synonym as another user, you must grant the CREATE TABLE and CREATE SYNONYM privileges to this user.

Log on as the SYS user and create a user named ny.

obclient> CREATE USER ny IDENTIFIED BY ***4**;
Query OK, 0 rows affected

Grant the CREATE TABLE privilege to the ny user.

obclient> GRANT CREATE TABLE TO ny;
Query OK, 0 rows affected

Grant the CREATE SYNONYM privilege to the ny user.

obclient> GRANT CREATE SYNONYM TO ny;
Query OK, 0 rows affected

Create a table named dws_ny as the ny user.

obclient> CREATE TABLE dws_ny(c1 NUMBER);
Query OK, 0 rows affected

Create a synonym named s1 as the ny user.

obclient> CREATE SYNONYM s1 FOR dws_ny;
Query OK, 0 rows affected

View the s1 synonym.

obclient> SELECT * FROM USER_SYNONYMS WHERE SYNONYM_NAME = 'S1';
+--------------+-------------+------------+---------+---------------+
| SYNONYM_NAME | TABLE_OWNER | TABLE_NAME | DB_LINK | ORIGIN_CON_ID |
+--------------+-------------+------------+---------+---------------+
| S1           | NY          | DWS_NY     | NULL    |             0 |
+--------------+-------------+------------+---------+---------------+
1 row in set

Note

If the ny user is not granted the CREATE TABLE and CREATE SYNONYM privileges, the ORA-00900: invalid SQL statement error is returned when you create a table or synonym as the ny user.

Example 2

Create a private synonym.

Create a table named dws_ny.

obclient> CREATE TABLE dws_ny(c1 NUMBER);
Query OK, 0 rows affected

Create a synonym named sp.

obclient> CREATE SYNONYM sp FOR dws_ny;
Query OK, 0 rows affected

Insert data into the dws_ny table by using the sp synonym.
```
obclient> INSERT INTO sp VALUES(1);
```

View the dws_ny table.

bclient> SELECT * FROM dws_ny;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set

Example 3

Create a public synonym.

Create a table named dws_ny1.

obclient> CREATE TABLE dws_ny1(c1 NUMBER);
Query OK, 0 rows affected

Create a synonym named spb.

obclient> CREATE PUBLIC SYNONYM spb FOR dws_ny1;
Query OK, 0 rows affected

View the spb synonym.

obclient> SELECT * FROM spb;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set