Oracle Database object_synonym

Oracle Database object_synonym

A synonym is a database object. It is an alias defined for a database object. The main purpose of synonyms is to simplify the writing of SQL statements.

Concepts and types of synonyms

Synonyms can be used to define an alias for an object of a user or an object of another user to simplify the writing of commands or programs. In a command or program, synonyms can be directly used to replace the original objects.

You can create synonyms for objects such as tables, views, stored programs, and sequences, create synonyms for a synonym, or even create synonyms for a non-existent object, the system verifies whether the object it represents exists only when synonym is used.
A synonym does not contain data or code in the original object. It serves only as a pointer.

When a synonym is used, the system searches for the object to which it points based on the definition of the synonym and converts the access to the synonym to the access to the original object.
Synonyms are used only to facilitate user operations on database objects.

Oracle supports two types of synonyms: private and public synonyms.

A private synonym is created by a common user and can only be used by the user by default.
Public synonyms are generally created by DBAs and can be used by all users.

The significance of public synonyms is that it represents an object that everyone can access.

Note: to access a public synonym, a common user must have the permission to access the original object pointed to by the synonym.

Generally, you can perform related operations after you have the permissions of the relevant database objects. However, if you have defined synonyms, You can simplify the writing of SQL statements.

For example, when accessing database objects in other user mode, you can omit the schema name before the object.

You can create a synonym for an object of another user at will, but this does not mean that the user has the permission to access the object of another user.

As mentioned above, the system only verifies that the user has access permissions when using synonyms.

For example:

Create synonym emp for scott. emp;

Select * from emp;

Note: Create a synonym for the database object emp in scott user mode as a normal user.

The error: ORA-00942: The table or view does not exist when no access is available.

Another scenario is to create synonyms for nonexistent objects:

Create synonym em for scott. em;

Select * from em;

When you use the synonym em to access the source object, the error: ORA-00980: synonym conversion is no longer valid.

Create and delete Synonyms

You can CREATE synonyms in your own mode. In this case, you must have the create synonym system permission.
If you want to CREATE a SYNONYM in another user's mode, you must have the create any synonym system permission.

If you want to CREATE a public synonym, you must have the create public synonym system permission.

The create synonym command is used to CREATE a private SYNONYM. Its syntax rules are as follows:

Create synonym for user name. Object Name;

Syntax format for creating public synonyms:

Create synonym for user name. Object Name;

Note: here the user name. Object Name refers to other user modes, because when creating synonyms, it is used to facilitate access to objects in other user modes.

You can also create synonyms in your own mode. For common users, creating a private synonym is meaningless.

For public synonyms, both common users and DBA-authorized users can create public synonyms in their own user mode, so that other users can access the database objects conveniently.

For example, the DBA permission user creates a public synonym for the dynamic performance View starting with v $.

If you do not use synonyms, you can delete them.

The command for deleting synonyms is drop synonym. The syntax format of this command is:
Drop synonym name;

Note: To delete a public synonym, add the PUBLIC keyword before the keyword SYNONYM.

You can delete a SYNONYM created by yourself. To delete a SYNONYM created by another user, you must have the permission to drop any synonym.
DBAs can delete all PUBLIC synonyms. Common users must have the permission to drop public synonym to delete PUBLIC synonyms.
After a synonym is deleted, its related information will also be deleted from the data dictionary.

Note: For common users, even if they create PUBLIC synonyms, they still need to have the permission to drop public synonym to delete their own synonyms.

 

Query synonym Information

A synonym is a database object. Its related information is stored in the data dictionary.

There are three data dictionaries related to synonyms: user_synonyms, all_synonyms, and dba_synonyms.

In the data dictionary user_synonyms, the synonyms of the current user are recorded. The column definitions of this table and their meanings are as follows:

SYNONYM_NAME

The owner of the object to which TABLE_OWNER points

Name of the object pointed to by TABLE_NAME

DB_LINK Database Link

Note: The DB_LINK column is obtained through DBLINK when the SYNONYM is created.

To query which synonyms are created by the current user and which objects each represent, you can run the following SELECT statement to query them:
SELECT synonym_name, table_owner, table_name FROM user_synonyms;

The data dictionary all_synonyms records all synonyms that the current user can use, including private synonyms and public synonyms.
All synonyms in the database are recorded in the data dictionary dba_synonyms, including private synonyms created by each user and public synonyms created by DBA.
This view can only be accessed by DBAs. In addition to all columns containing the data dictionary user synonyms, a column owner represents the creator of the synonym.
To query the information of a synonym in the entire database, you can query the data dictionary dba_synonyms.
For example, to query all synonyms created by scott, run the following SELECT statement:

SELECT synonym_name, table_owner, table_name FROM dba_synonyms WHERE owner = 'Scott ';

If you want to query the synonyms of user scott's table dept, You can execute the following SELECT statement:

SELECT synonym_name, table_owner FROM dba_synonyms WHERE owner = 'Scott 'AND table_name = 'emp ';

To query all public synonyms in the system, run the following SELECT statement:

SELECT synonym_name, table_owner FROM dba_synonyms WHERE owner = 'public ';

Note: The difference between table_owner and owner is as follows:

Table_owner refers to the owner of the database object to which the synonym points. It generally refers to the creator of the object, and the owner refers to the creator of the synonym.