Create a synonym

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

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.

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

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

2. Grant the CREATE TABLE privilege to the ny user.

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

3. Grant the CREATE SYNONYM privilege to the ny user.

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

4. Create a table named dws_ny as the ny user.

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

5. Create a synonym named s1 as the ny user.

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

6. 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.

1. Create a table named dws_ny.

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

2. Create a synonym named sp.

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

3. Insert data into the dws_ny table by using the sp synonym.

   obclient> INSERT INTO sp VALUES(1);
   

4. View the dws_ny table.

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

Example 3

Create a public synonym.

1. Create a table named dws_ny1.

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

2. Create a synonym named spb.

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

3. View the spb synonym.

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