Create a dblink in Oracle

Confusing terms:
Db_name:The unique identifier of a database (Oracle database), which is the Oracle database described in Chapter 1. This expression is sufficient for a single database, but with the popularization of distributed databases composed of multiple databases, this method of Command database creates a certain burden on database management, because the names of various databases may be the same, resulting in management confusion. To solve this problem, the db_domain parameter is introduced. In this way, the database ID is determined by the two parameters db_name and db_domain, avoiding management confusion caused by database name duplication. This is similar to managing machine names on the Internet. We connect the parameters db_name and db_domain with '.' to indicate a database, and the database name is global_name, that is, it extends db_name. The db_name parameter can only contain letters, numbers, '_', '#', and '$', and can contain a maximum of 8 characters.

Db_domain:Define the domain where a database is located. The domain name has nothing to do with the 'region' on the Internet. It is determined by the database administrator to better manage the distributed database. Of course, to facilitate management, it can be equal to the domain of the Internet.

Global_name:For the unique identifier of a database (Oracle database), we recommend that you use this method to command the database. This value is determined when you create a database. The default value is db_name. db_domain. Any modifications to the parameters of db_name and db_domain In the parameter file will not affect the value of global_name. To modify global_name, you can only use the alter database rename global_name to <db_name.db_domain> command to modify it, modify the parameters.

SERVICE_NAME:This parameter is newly introduced by Oracle8i. Before 8i, we used Sid to identify an instance of the database. However, in the parallel environment of Oracle, a database corresponds to multiple instances, so that multiple network service names are required, configuration is cumbersome. To facilitate the setting in the parallel environment, the SERVICE_NAME parameter is introduced, which corresponds to a database rather than an instance, and has many other advantages. The default value of this parameter is db_name. db_domain, which is equal to global_name. A database can correspond to multiple service_names for more flexible configuration. This parameter has no direct relationship with Sid, that is, the service name must be the same as the SID.

Net service name:The Network Service name, also known as the database alias (Database alias ). Yes ClientProgramIt is required to access the database. It shields the details of how the client connects to the server and achieves the database location transparency.

 

An instance is the name used to manage the memory structure of relevant databases (consisting of SGA, PGA, server processes, user processes, and background processes)

A database is a file (data file, log file, control file, etc.) on the actual disk. It stores data, but its data is operated by the corresponding instance.

The service name is the name published to the outside world. It is the network listening service.

In fact, in our traditional concept, databases are collectively referred to as names. In Oracle, you can think of "databases" as a big concept, but also a small concept.

1. Several databases can be installed in an Oracle database system at the same time. Each database corresponds to a unique

One instance, except for the OPS System, multiple instances can operate on one database at the same time. This is called a parallel service.

Tool

2. It is just a name. Sid is instance_name, and service_names is mainly used in the listener,

For convenience, some are for the continuation of traditional habits, and some are for more convenient use.

3. net easy config is used to manipulate host strings and serve clients.

One database can publish multiple service names (service_names)

A client can also use multiple host strings to connect to the same database server.

4. Multiple Oracle databases (small concept) can be installed on one OS. Each database can publish multiple servers.

The service names are all implemented through init. ora and listener. ora.

-----------------------------------------------------

1. Create a net service name

Go

CD $ ORACLE_HOME/Network/admin

VI tnsnames. ora

Add

Ccpbs_19 =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 10.130.38.19) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = ccpbs)
)
)

Save and exit and Test

> Tnsping ccpbs_19

 

TNS Ping utility for Linux: Version 9.2.0.7.0-production on 02-jul-2009 15:16:52

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:

Used tnsnames adapter to resolve the alias
Attempting to contact (description = (address_list = (address = (Protocol = TCP) (host = 10.130.38.19) (Port = 1521) (CONNECT_DATA = (SERVICE_NAME = ccpbs )))
OK (20 msec)

Test successful

See http://article.pchome.net/content-340501.html in Windows

 

2, Create a database link,

 

/Home/Oracle/> sqlplus/nolog

SQL * Plus: Release 9.2.0.7.0-production on Thu Jul 2 16:01:00 2009

Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

SQL> Conn/As sysdba
Connected.

Go to System AdministratorSQL>Run the following command under the operator:

> Create public database link ecbm_19 connect to ecbm_username identified by ecbm_pwd using 'cpbs _ 19 ';

Database link created.

Format:

Create Public Database Link Link_name Connect To User Identified By Password using 'Sid' ;

A connection is created to connect to the ecbm_username user in 10.130.38.19 ccpbs.

When querying data with @ ecbm_19, it is OK, for example, select * from EMP @ ecbm_19;

Delete drop Public Database LinkEcbm_19;

 

 

3To make distributed operations more transparent,OracleObjects with synonyms in the databaseSynonym

SQL> Create synonym bjscottemp for EMP @ Beijing;

So you can useBjscottempTo replace@Distributed Link operation of symbolsEMP @ Beijing.

Create Public synonym Poh for po_headers_all @ erp_danzheng_test ;

Create Public synonym PAPF for per_all_people @ erp_danzheng_test ;

Create Public SynonymPVForPo_vendors@Erp_danzheng_test;

 

 

4, View all the database links, enter the system administratorSQL>Run the following command under the operator:

SQL> select owner, object_name from dba_objects where object_type = 'database link ';

 

Reference http://moonsoft.itpub.net/post/15182/125559