Oracle Database Synonyms

Oracle Database Synonyms

I. Definition

A synonym is an alias for a database solution object. The database solution objects here refer to tables, views, sequences, stored procedures, packages, and so on.

2. Benefits of synonyms

1. No memory space occupied, saving a lot of database space

2. simplified access to database objects

3. Improved Database Object Access Security

4. the extended scope of use of databases enables seamless interaction between different database users. synonyms can be created on different database servers and connected over the network.

3. Create synonym syntax

create public synonym table_a for  user.table_a;

Iv. Delete synonym syntax

Because synonyms are also objects, the same is true for deleting syntaxes.

drop public synonym table_a;

5. Expansion

If you want to access table_a tables in different databases or users, you can also use synonyms, but you need to create a Database Link (Database connection) to expand the access, then, use the following statement to create a database synonym:

create synonym table_a for table_a @DB_Link;

Vi. instance demonstration

1. demo of different users in the same database

The table employees under the ctdev user

However, the ctcheck user does not have this table, and the ctcheck user cannot access the table employees;

Create Synonym

create public synonym employees for ctdev.employees;

After creation, you can access the table employees of the ctdev user under the ctcheck user.

2. Cross-database synonym demonstration

User cwuser in database 10.248.100.81 cannot access employees, as shown in.

It is known that the name of the dblink of the user 10.1.2.1 user ctdev under the user cwuser user under the database 10.248.100.81 is to_ctdev. As shown in

Create a synonym for accessing the employees table in ctdev under the cwuser user.

create synonym employees for employees@TO_CTDEV

You can access the table employees under ctdev under cwuser, as shown in