Basic methods for creating synonyms in Oracle and possible problems

Many customers have their own database servers to manage their important data. What if we need to use the data in our project and the customer cannot insert the data to our project database server? One solution is to create some table or view synonyms based on the customer's key data server on the project database server. Here we will introduce how to create a synonym for Oracle Based on the Remote Server View in my project.

1. Create a database link. Two different database servers read the data of a user under the database server of the customer's key data from one user of the project database server. dblink can be used in this case. Assume that the database server information provided by the customer is as follows:
The syntax for creating a database link is:
Configure the local service first. Open the TNSNAMES. ORA Network Configuration File: D: \ ORA \ NETWORK \ ADMIN \ tnsnames. ora File according to your own machine ),
LIMSTQ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.107.6.15) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = VGSM)
)
)
Do not leave spaces before LIMSTQ ).

Then, create dblink. When the database parameter global_name = true, the Database Link name must be the same as that of the remote customer database. We must first confirm. The statement for viewing this parameter is as follows: if it is true, remember that the created dblink is the same as that of the remote database. Fortunately, the value of global_name is false.

You can run the following command to query the global name of a database: create database link limstq.us.oracle.com connect to jlgl identified by jlgl using 'limstq ';

2. Creating synonyms in Oracle is simple. The syntax for creating synonyms in Oracle is as follows:
The created synonym syntax is: create synonym jl_gljs_tb_lims for JLGL_LIMS@limstq.us.oracle.com;

3. After creating a synonym for Oracle, you can use select jl_gljs_tb_lims on the project database server to search for customers' key data. If possible, create another view in the following format: create or replace view jl_gljs_limstq_vw as select * from jl_gljs_tb_lims; in this way, the process of creating a synonym Based on the Remote Server view is complete.

Possible problems:If you find the following non-connection error during synonym creation in Oracle, it will be very depressing. ORA-12154: TNS: the specified connection identifier cannot be resolved. You can view the ora file and find that you have configured TNS. This is also a headache for me. You must check whether the local service is created in the ora file of the master server of the project database server, rather than the ora file of the client. You may be using pl/SQL or SQL plus tools, but the machine you use is a client, rather than a real project master server, unless you create oracle on your own machine ). It is OK only when you find the ora file of the master server and configure the local service. In this case, you can easily access some key data in your database server by searching for synonyms or creating new views in your project.

1. Solutions that you have to look at when creating a stored procedure in Oracle
2. The most effective method for creating stored procedures in Oracle
3. Example code for creating a tablespace in Oracle
4. Procedure for creating and deleting a user in Oracle
5. How to Create a tablespace in Oracle