Oracle Create synonym __oracle

Most customers have their own database servers to manage some of their key data. So what if we need to use this data in our project and the customer is not allowed to insert that data into our project database server? One solution is to create a synonym for some tables or views on the Project database server based on the customer's critical data server. Let me introduce the process I made in the project based on how the remote server view creates synonyms.

First, create the database link. Two different database servers, from the Project database server, a user reading the key data from the database server under a user's data, this time can use Dblink. Assume that the database server information provided by the customer is as follows:

The syntax for creating a database link is:

At this point, you first configure the local service. Open the Tnsnames.ora network Configuration File:d:/ora/network/admin/tnsnames.ora file (depending on the circumstances of your machine) and will

LIMSTQ =
(DESCRIPTION =
(Address_list =
(address = (PROTOCOL = TCP) (HOST = 10.107.6.15) (PORT = 1521))
)
(Connect_data =
(service_name = vgsm)
)
)

Add to the back of this file (note that LIMSTQ is not preceded by a blank).

Then, create the Dblink. When database parameters are global_name=true, the database link name needs to be the same as the remote client database name. We have to make sure first. The statement that views this parameter is as follows:

If it is ture, remember to create the Dblink name and the remote side of the database, very fortunate, I used the database parameter Global_name value is false yes.

The database global name can be identified by the following command:

Querying tables in the remote database:

At this time, I created my own databae link:create database link limstq.us.oracle.com connect to JLGL identified by JLGL using ' LIMSTQ ';

Second, the creation of synonyms, very simple oh. The syntax for establishing synonyms is as follows:

The synonym syntax I established is: create synonym jl_gljs_tb_lims for JLGL_LIMS@limstq.us.oracle.com;

After the synonym is successfully created, it is possible to find operations on the client's critical data by select Jl_gljs_tb_lims on the project database server. However, if possible, it is a good idea to create another view in the form of: Create or Replace View JL_GLJS_LIMSTQ_VW as SELECT * from Jl_gljs_tb_lims;

This completes the synonym creation process based on the remote server view.

Wait a minute, if you found something like the following, it would be frustrating. Ora-12154:tns: The specified connection identifier could not be resolved.

And you find yourself with TNS by looking at the Ora file. This is also a problem that gives me a headache. You must see clearly that the local service is created in the Ora file of the primary server of the Project database server, not in the Ora file of your own client. You may be using Pl/sql or SQL Plus, but the machine you use is the client, not the real project master (unless you are creating a master Oracle on your own machine). Only find the main server ora file, the Local service configuration to the inside, it is OK. At this point, you can easily access some of the key data from your client's database server by looking for synonyms or a new view in your project.

GRANT CREATE synonym to Gisap;
CREATE synonym unit for unit@navtechlink. Regress. Rdbms. DEV. US. oracle.com;
CREATE synonym VEHICLE for vehicle@navtechlink. Regress. Rdbms. DEV. US. oracle.com;
CREATE synonym HISTORY for history@navtechlink. Regress. Rdbms. DEV. US. oracle.com;