ODC supports visualized synonym creation. This topic describes how to create a synonym with ODC.
Overview
A synonym is an alias that you create for a database object of another user. You can perform operations on the object by querying and operating the synonym.
Synonyms are similar to views. You can use synonyms to add, delete, modify, and query table data. You can define synonyms for most database objects in OceanBase Database, such as tables, views, materialized views, sequences, functions, stored procedures, packages, and synonyms.
Synonyms extend the availability of database objects and allow them to be accessed by different database users with the required privileges. This design simplifies and secures access to database objects.
Synonym types
OceanBase Database supports synonyms and public synonyms.
Synonyms: In general, a synonym created by a regular user is a private synonym. A private synonym can be accessed by the user who created it or other users who are granted the required privileges. To create a private synonym under the current user, you must have the CREATE SYNONYM privilege.
Public synonyms: A public synonym is created by a user who is granted the administrator role, such as the system user account, and can be accessed by all users.
For more information, see Synonyms.
As shown in the preceding figure, you can create a synonym in the following eight steps:
Procedure
In this example, a synonym named syn_employee is created in the OceanBase Developer Center (ODC) console for an employee table in the SYS database. Procedure:
Step 1: Specify the name of the synonym.
Log on to the ODC console and click the name of the desired connection to go to the corresponding connection management page. You can click Synonym in the left-side navigation pane to get a list of synonyms and a list of public synonyms. To create a synonym, click + in the upper-right corner of the synonym list or choose Create > Synonym in the top navigation bar.
In the Create Synonym dialog box, specify the name of the synonym.
Step 2: Specify the object owner.
Specify the schema where the object is located.
Step 3: Specify the object type.
Specify the object type. Valid values: Table and View.
Step 4: Specify the object name.
Specify the name of the object for which the synonym is created.
Step 5: Specify the synonym type.
Specify the synonym type. Valid values: Synonym and Public Synonym.
Step 6: Verify the information about the synonym.
Click Next: Verify SQL Statement to go to the Create Synonym page.
Step 7: Edit the synonym.
Edit the statement on the Create Synonym page.
On the statement editing page, a synonym definition statement is generated based on the information specified in the Create Synonym dialog box. You can modify this statement. After you confirm the statement, click the Create button in the upper-right corner of the page to complete the synonym creation. A synonym cannot be modified after it is created.
Before a synonym is created, you can edit the SQL statements of the synonym on the Create Synonym page. Syntax:
CREATE OR REPLACE SYNONYM syn_name FOR schema_name.object_name;
Parameters
Parameter | Description |
OR REPLACE | Updates a synonym if the synonym already exists. |
syn_name | The name of the synonym. The name of a synonym must be unique in the specified schema. |
schema_name | The database schema to which the object belongs. |
object_name | The name of the object. |
Step 8: Complete the synonym creation.
Click Create. After you create a synonym, you can use it in the same way as a built-in synonym.
To manage a synonym, right-click the synonym name in the left-side navigation pane, and select the required operation from the context menu, which provides the following options: View, Create, Delete, Download, and Refresh.
For more information, see Manage synonyms.
Syntax:
SELECT * FROM syn_name ([syn_parameter[,...])
Example:
SELECT * FROM syn_employee;
