Oracle Learning article Seven: synonyms

The functionality of synonym management is provided in the Oracle database. Oracle synonyms are an alias for database schema objects and are often used to simplify object access and improve the security of object access.

The management of the user in Oracle is managed using permissions, that is, if we want to use the database, we must have permission, but if someone else has granted us the rights,
We can also operate on the database, but we have to type the name of the table owner before the name of the table that is authorized, so this is more troublesome, and in this case, what should we do?
Create a synonym for Oracle! This allows us to use the table directly using synonyms.

1. The concept of synonyms
The functionality of synonym management is provided in the Oracle database. Synonyms are an alias for database schema objects and are often used to simplify object access and improve the security of object access. When using synonyms, the Oracle database translates it into the name of the corresponding schema object. Similar to views, synonyms do not occupy the actual storage space, only the definition of synonyms is saved in the data dictionary. Most database objects in the Oracle database, such as tables, views, synonyms, sequences, stored procedures, packages, and so on, can be defined by the database administrator as a synonym for their actual situation.

Classification of 2.Oracle synonyms
There are two types of Oracle synonyms, the public Oracle synonym and the private Oracle synonym, respectively.
1) Common Oracle Synonyms: owned by a special user group public. As the name implies, all users in the database can use common synonyms. Common synonyms are often used to mark more common database objects, which are often referred to by everyone.
2) Private Oracle synonym: It is the same as the common synonym, and he is owned by the user who created him. Of course, the creator of this synonym can control whether other users have access to their own private synonyms by authorizing them.

3.Oracle Synonyms creation and deletion
Create a syntax for a public Oracle synonym: Create [common] synonym synonym name for [username.] ObjectName;
Drop [public] synonym synonym name

The role of 4.Oracle synonyms
1) in multi-user collaborative development, the name of the object and its holder can be masked. If there is no synonym, when you manipulate other users ' tables, The user name must be hidden after using the Oracle synonym in the form of the user name. Object name, of course, it is important to note that the public synonym simply defines a common alias for the database object, whether the other user can access the database object through the alias, or if the user is authorized.
2) Simplify the SQL statement for the user. The above one is actually a simplified SQL embodiment, and if the name of the table you built is very long, you can create a Oracle synonym for this table to simplify SQL development.
3) provides location transparency for remote objects in a distributed database.
The role of 5.Oracle synonyms in database chain
A database chain is a named object that describes the path of one database to another, through which communication between different databases can be implemented.
Create database link name connect to user name identified by password using ' Oracle connection string '; The object name must be accessed through the database chain name. The role of synonyms in the database chain is to provide location transparency.

5. Common usage of synonyms is as follows:

CREATE [OR REPLACE] [public] synonym [schema.] Synonym name for [schema.] Object [@dblink];

--1 "proprietary (private) synonyms

CREATE synonym sysn_test for TEST; (Grant the account the right to create synonyms: Grant create any synonym to DM;)

--2 "Public synonyms

Create public synonym public_test for TEST; (permissions to create synonyms for the account: Grant create public synonym to DM;)

--3 "Also if you want to create a synonym for a table on a remote database, you need to create a database link to extend the access.
Then use the following statement to create a database synonym: "CREATE synonym table_name for [email protected]_link;

6. Use synonyms:
SELECT * from Sysn_test;

Using synonyms ensures that the application's code remains stable when the location or object name of the database changes, and only the synonyms need to be changed;

When using a synonym that does not specify a schema, first look in the user's own schema and then look in the public synonym

7. Delete synonyms

DROP [public] synonym [schema.] Synonym name [Force];

DROP synonym Sysn_test;

Drop public synonym public_test;--when the original object of the synonym is deleted, the synonym is not deleted

8. Compiling synonyms

ALTER synonym T COMPILE; --When the original object of the synonym is re-established, the synonym needs to be recompiled

Oracle Learning article Seven: synonyms