Synonyms of Oracle literally means alias. Similar to the function of view, it is a kind of mapping relationship, hiding the name and owner of the object, providing location transparency for remote objects in distributed database

1. Create synonymous sentences

create public synonym table_ Name (rename) for user.table_ name;

Create a synonym for a table in a remote database
create a database link to extend access, and then create a synonym for the database using the following statement:
create a synonym for a table in a remote database

　create synonym table_ name for table_ name@DB_ Link;

–1. If you need to create a global dblink, you need to make sure that the user has permission to create a dblink

select * from user_ sys_ privs

where privilege like upper(‘%DATABASE LINK%’);

–2. If not, you need to use SYSDBA role to empower users

grant create public database link to dbusername;

–3. Log in to PL/SQL with the following command:

— the first one: the mapping of database B in tnsnames.ora on database server a is required

Create database link database link name connect to user name identified by password using ‘instance name of locally configured data’

— the second: direct configuration, using PL/SQL

If you want to create a global dblink, you must use systm or sys user and add public before database

create /* public */ database link dblink1

　　connect to dbusername identified by dbpassword

　　using ‘(DESCRIPTION =(ADDRESS_ LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_ DATA =(SERVICE_ NAME = orcl)))’;

–Database parameter Global_ When name = true, the database link name should be the same as the remote database name. The global name of the database can be found with the following command

select * from global_ name;

–Querying, deleting and inserting data are the same as operating the local database, except that the table name needs to be written as “table name @ dblink server”

Select xxx from table name @ database link name

–5 delete dblink

drop /* public */ database link dblink1;

–6 create and delete synonym

Create or replace synonym name for table name

Create or replace synonym for user. Table name

Create or replace synonym name for table name @ database link name

Drop [public] synonym

–7 create and delete views

Create or replace view name as

(select field from user. Table name @ dblink1)

Drop view view name

–8 note:

Creating a dblink is very simple, but a lock appears in the background during use. To view the lock, you can go to the console to see or query the database. Every time you use dblink query, you will create a connection with the remote database. Dblink should not release this connection automatically. If you use dblink query heavily, it will cause the number of connections of Web project is not enough, which will cause the system not to run normally and the system not to run normally

–User authorization

grant select/delete/update on user2

–View all synonyms:

select * from dba_ synonyms

Synonyms have the following advantages: save a lot of database space, and there is not much difference in the operation of the same table for different users; The application scope of the extended database can realize seamless interaction between different database users; Synonyms can be created on different database servers and connected through the network
Oracle synonym is an alias of database scheme objects, which is often used to simplify object access and improve the security of object access Oracle database provides the function of synonym management. Synonym is an alias of database scheme object, which is often used to simplify object access and improve the security of object access. When a synonym is used, the Oracle database translates it into the name of the corresponding schema object. Similar to the view, synonyms do not occupy the actual storage space, only the definition of synonyms is saved in the data dictionary. Most database objects in Oracle database, such as tables, views, synonyms, sequences, stored procedures, packages, etc., can be defined by database administrators according to the actual situation 1) public Oracle synonym: owned by a special user group, public. As the name suggests, all users in the database can use common synonyms. Common synonyms are often used to identify some common database objects, which need to be referenced
2) private Oracle synonym: it corresponds to the public synonym, which is owned by the user who created it. Of course, the creator of this synonym can control whether other users have the right to use their own private synonyms through authorization
3. Create and delete Oracle synonyms
the syntax of creating public Oracle synonyms: create [public] synonym name for [user name.] objectname
drop [public] synonym name
4. The role of Oracle synonym
1) in multi-user collaborative development, the name of an object and its holder can be masked. If there is no synonym, you must use the form of user name and object name when operating other users’ tables. After using Oracle synonyms, you can hide the user name. Of course, it should be noted that the public synonym only defines a public alias for the database object. Can other users access the database object through this alias, It also depends on whether the user is authorized
2) simplify SQL statements for users. The above one is actually an embodiment of simplifying SQL. At the same time, if the name of the table you create is very long, you can create an Oracle synonym for the table to simplify SQL development
3) provide location transparency for remote objects in distributed database
5. The role of Oracle synonyms in database chain
database chain is a named object, which indicates the path from one database to another, through which communication between different databases can be realized
create database link database chain name connect to user name identified by password using ‘Oracle connection string’; The object name @ database chain name is used to access the object. The function of synonyms in database chain is to provide location transparency

The solution to the actual scenario: create public synchronization BD_ INFO for BDYMUSER. BD_ INFO。