Oracle dblink Parsing

Dblink between database versions
Server Version
Client 11.1.0 10.2.0 10.1.0 9.2.0 9.0.1 8.1.7 8.1.6 8.1.5 8.0.6 8.0.5 7.3.4
11.1.0 Yes #6 Yes #6 ES #5 No #3 No #3 No #3 No #3 No #3 No #3
10.2.0 Yes #6 Yes ES #5 No Was No #3 No #3 No #3 No #3 No #3
10.1.0 (#4) Yes #6 Yes ES Was #2 No #3 No #3 No #3 No #3 No #3 No #3
9.2.0 ES #5 ES #5 ES Was No #1
9.0.1 No Was
8.1.7 No Was
8.1.6 No Was
8.1.5 No Was
8.0.6 No Was
8.0.5 No Was
7.3.4 No Was
 
Multiple ORACLE databases physically stored in the network can be logically considered as a single large database. Users can access the data in the remote database through the network, the collaborative processing between servers is completely transparent to workstation users and applications, developers do not need to care about the network connection details, the specific distribution of data on network nodes, and the coordination process between servers.
The links between databases are established on the database link. To create a database link, you must first set the LINK string on the DATABASE that creates the LINK, that is, configure the local network service name of a remote DATABASE.
For the configuration of the link string, see the client connection server section.

You can run the following command to check the Global Database Name:
SELECT * FROM GLOBAL_NAME;
You can use the following statement to modify the parameter value:
Alter system set GLOBAL_NAME = TRUE/FALSE;
<1> when the database parameter global_name is set to false, the Database Link name is not required to be the same as that of the remote database.
Connect and communicate between oracle databases.
The syntax for creating a database link is as follows:
CREATE [PUBLIC] database link link
Connect to username identified by password
USING 'string string'
Where:
-- Demona is the connection string created with net8 easy config.
Set global_names In the init. ora file of the target database to false.
Restart database settings to take effect
Or run the command as sys.
Note: The account used to CREATE the database link must have the system permission of create database link or create public database link. The account used to log on to the remote DATABASE must have the create session permission. Both permissions are included in the CONNECT role (create public database link permission is included in DBA ).
A public database link is available to all users in the database, while a private link is only available to users who create it. It is impossible for a user to authorize a private database link to another user. A database link is either public or private.
When creating a database link, you can also use the default logon mode, that is, do not specify the remote database user name and password:
Create public database link zrhs_link
Using 'zrhs ';
If the user name and password are not specified, ORACLE uses the current user name and password to log on to the remote database.
USING is followed by a link string, that is, the network service name of the remote database. This service name is stored in TNSNAMES. the ORA file defines the protocol, host name, port, and database name.
The statement for deleting a database link is:
DROP [PUBLIC] database link zrhs_link
Database link reference
Generally, when you reference a database link, you can directly put it behind the called table name or view name, and use a @ in the middle as the delimiter:
SELECT * FROM worker @ zrhs_link;
For frequently used database links, you can create a local synonym for ease of use:
Create synonym worker_syn FOR worker @ zrhs_link;
You can also create a local remote view for ease of use:
Create view worker as select * FROM worker @ zrhs_link where... ;
Now this view can be treated like any other view in the local database, and can be authorized to other users to access this view, but this user must have the permission to access the database link.
In another case, the table to be accessed is not under the remote account specified in the database link, but the account has the permission to access the table, add the table username before the table name:
SELECT * FROM camel. worker @ zrhs_link;
<2> when the database parameter global_name = true, the database connection name must be the same as the remote database name.
You can use the following command to check the global name of the database:
SELECT * FROM GLOBAL_NAME;
Atabase link (dblink) -- Database link
Syntax for creating dblink:
SQL> create database link name
Connect to username identified by password
Using 'host string name ';
For example:
SQL> create database link ora9i.us.oracle.com ### here, us.oracle.com is the default domain name of oracle ###
Connect to scott identified by tiger
Using 'sun ';
1) The dblink name must be the same as the global database name (global_name) of the remote database;
2) User name. The password is the remote database user name and password;
3) The host string is the string defined in tnsnames. ora;
4) dblink cannot be created between two databases with the same name;
Then, you can access the remote database through dblink.
For example:
SQL> select * from table name @ ora9i.us.oracle.com;
You can also create snapshots to automatically transmit remote data through dblink.
View All database links, go to the system administrator SQL> operator, and run the following command:

SQL> select owner, object_name from dba_objects where object_type = 'database link ';
========================================================== ======================================
Confusing terms:
Db_name: the unique identifier of a database (Oracle database). This database is the Oracle database mentioned 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: defines 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 in order to better manage the distributed database. Of course, to facilitate management, it can be equal to the domain of the Internet.

Global_name: 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 name of the network service, also known as the database alias (database alias ). It is required when the client program accesses the database. It shields the details of how the client connects to the server and achieves the location transparency of the database.
 
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 the system administrator SQL> operator and run the following command:
> 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 link ecbm_19;
 
 
3. Create synonyms. In order to make distributed operations more transparent, the ORACLE database has a synonym object synonym.
　　
SQL> create synonym bjscottemp for emp @ beijing;
　　
Therefore, you can use bjscottemp to replace the Distributed Link operation with the @ symbol emp @ 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 synonym pv for po_vendors @ erp_danzheng_test;
 
 
4. View All database links, go to the system administrator SQL> operator, and run the following command:
　　
SQL> select owner, object_name from dba_objects where object_type = 'database link ';
 
Reference http://moonsoft.itpub.net/post/15182/125559
 
 
Test conditions:
Assume that a company is headquartered in Beijing and has a subsidiary in Xinjiang. In this test, the computer in Xinjiang is a local computer, that is, the required IP address is 192.168.1.100.
The headquarters in Beijing has a centralized database with the sid sidbj, Username: userbj, password: bj123, and Beijing IP Address: 192.168.1.101.
A local branch in Xinjiang also has a database whose SID is SIDXJ, user: userxj, password: xj123, and IP address of Xinjiang is 192.168.1.100.
Access Data in the SIDXJ database in the local Xinjiang region to the SIDBJ database in Beijing.
That is to say, in the sidxj database, the user userxj (192.168.1.100) needs to establish a DBLINK to access the data in sidBJ (192.168.1.101) as userbj.
Test environment: Both databases are built on WINXP, and ORACLE versions are Oracle817.
When creating an environment, you must disable the Windows Firewall on the two computers. Otherwise, the ping may fail, but the Oracle connection may fail.
1. Q: How to return the GLOBAL_NAME of the database?
Execute SELECT * FROM GLOBAL_NAME;
The GLOBAL_NAME of the database in Beijing is SIDBJ. US. ORACLE. COM.
The GLOBAL_NAME of the database in Xinjiang is SIDXJ.
2. Q: How can I check whether the Global_name parameter is true or False?
A: run SQL> show parameter global_name;
The execution result is as follows:
NAME TYPE VALUE
-----------------------------------------------------------------------------
Global_names boolean TRUE
This parameter is true.
If this parameter is set to true, the name of the locally created DBLINK must be the same as that of the remote Global_name.
3. Q: Check whether the remote data producer supports the advanced copy function.
A: Check the v $ option view. If Advanced replication is TRUE, Advanced replication is supported. Otherwise, Advanced replication is not supported.
The execution statement is: select * from v $ option;
You can also run the select * from v $ option where PARAMETER = 'advanced replicase' statement,
If the returned value is True, it is supported. Otherwise, it is not supported. Check whether the two databases support this function.

Procedure:
1. Create an Oracle client locally to connect to tns_xj_to_bj, which is used to connect to the database in Beijing.
Create a table in the database in Beijing for testing.
Create table USERBJ. BJ_TEST
(
STU_ID NUMBER,
STU_NAME VARCHAR2 (100)
)
Add a record:
Insert into BJ_TEST (STU_ID, STU_NAME)
Values (1, 'zhong derong ');
 
 
2. Create a connection tnsxj to connect to the local sidxj database and log on to PLSQL using tnsxj, userxj, and xj123.
Create a remote data connection DBLink to Beijing.
Create database link SIDBJ. US. ORACLE. COM connect to userbj identified by bj123 using 'tns _ xj_to_bj ';
Among them: SIDBJ. US. ORACLE. COM is the global_name of the remote database, userbj is the username used to connect to SIDBJ, and bj123 is the password of userbj,
Tns_xj_to_bj is the service name of the local database connected to Beijing.
3. test whether the connection is successful:
Select * from dual@SIDBJ.US.ORACLE.COM
If the following result is returned, the connection is successful.
DUMMY
-----
X
4. query the created remote connection name in the local data:
SQL> select owner, object_name from dba_objects where object_type = 'database link ';
OWNER OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
System sidbj. US. ORACLE. COM
USERXJ DBLINK_XJ_TO_BJ
USERXJ SIDBJ

5. So far, a dblink is established on the computer in Xinjiang to connect to the database in Beijing,
Query the table information in the database of Beijing in the database of Xinjiang:
Select * from bj_test@SIDBJ.US.ORACLE.COM
Query results:
STU_ID STU_NAME
------------------------------------------------------------------------------------------
1 o'clock derong
Indicates that the query is normal.

6. Test the global_name parameter.
Create database link a connect to userbj identified by bj123 using 'tns _ xj_to_bj ';
A dblink is created and the following query is executed:
Select owner, object_name from dba_objects where object_type = 'database link ';
You can see that a DBLINK named a has been created.
However, when you perform the following query to retrieve remote data, an error occurs.
Select * from bj_test @
Because the value of the global_names parameter in the local database is true, the dblink name must be the same as that in the remote database.
The global_names parameter of the local database to be modified is set to false. (Note that the parameter of the local database SIDXJ cannot be modified by testing. It does not matter whether the global_names parameter of the process is set to TRUE or FALSE)
Use the following statement to modify the parameter:
SQL> alter system set global_names = false;
Perform the following steps to query the data:
Create database link a connect to userbj identified by bj123 using 'tns _ xj_to_bj '; -- create DBLINK
Select owner, object_name from dba_objects where object_type = 'database link'; -- query the existing DBLINK

Select * from bj_test @ a -- query a remote table
7. The following describes how to create a storage process in SIDXJ and use DBLINK to retrieve the remote database SIDBJ. The storage process is as follows:
Create or replace procedure test_cur
As
StrSql1 varchar2 (1000 );
T_stu_name varchar2 (100 );
Type tcur is ref cursor;
Cur tcur;
Begin
StrSql1: = 'select stu_name from bj_test @ ';
Open cur for strSql1;
LOOP
Fetch cur into t_stu_name;
Exit when cur % NOTFOUND;
DBMS_OUTPUT.PUT_LINE (t_stu_name );
End loop;
Close cur;
End test_cur;
The stored procedure test shows that the value of STU_NAME in the process database is output.

All the above scripts passed the test in ORACLE817.

Author "thomas0988"