Detailed step records for Oracle to establish Dblink

Test conditions:
Suppose a company is headquartered in Beijing, Xinjiang has its subordinate branch. In this test, the computer in Xinjiang for the local computer, that is, the IP address is: 192.168.1.100

Beijing's headquarters has a centralized database whose SID is SIDBJ, username: userbj, Password: bj123, the IP address of Beijing is: 192.168.1.101.
In the local (Xinjiang) branch also has a database, its SID is Sidxj, User: USERXJ, Password: xj123, Xinjiang's IP address is: 192.168.1.100.
The data in the database SIDBJ in Beijing will be accessed from the local Xinjiang SIDXJ database.
In other words, in the SIDXJ database, the user userxj (192.168.1.100) needs to establish dblink to access the data in USERBJ (SIDBJ) as a 192.168.1.101 user.

Test environment: Two databases are built on WinXP and Oracle versions are Oracle817
When setting up the environment, pay attention to shutting down the Windows Firewall on the two computers, otherwise, there will be a situation where you can ping, but the Oracle is not connected.

1, Q: How to return the database Global_name?
Execute select * from Global_name;
The global_name of the Beijing database is SIDBJ.US.ORACLE.COM
The global_name of database in Xinjiang is SIDXJ

2. Q: How do I see if the Global_name parameter is True or false?
Answer: Execute:sql> show parameter global_name;
The results of the implementation are as follows:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Global_names Boolean TRUE

Indicates that the parameter is true.

When this parameter is true, the name of the dblink that you establish locally must be the same as the remote Global_name.

3. Q: See if remote Data irresolute supports advanced replication capabilities.

A: The advanced replication feature is supported by viewing the v$option view, if it is replication true, otherwise it is not supported.
The execution statement is: SELECT * from V$option;

You can also execute a select * from V$option where parameter= ' advanced replication ' statement,
How to return a value of true is supported, otherwise it is not supported. In all two databases, check whether support is the only line.

Setup steps:

1. Establish an Oracle Client connection TNS_XJ_TO_BJ on-premises to connect to the Beijing database.

In the database in Beijing, create a table for testing.
CREATE TABLE USERBJ. Bj_test
(
STU_ID number,
Stu_name VARCHAR2 (100)
)
Add a record to it:
Insert into Bj_test (stu_id, Stu_name)
VALUES (1, ' Zhongderong ');

2. Create a new connection tnsxj to connect to the local SIDXJ database, tnsxj/userxj/xj123 log in to Plsql.

The following begins the creation of a remote data connection Dblink connected to Beijing.
Create DATABASE link SIDBJ. US. Oracle.com connect to USERBJ identified by bj123 using ' TNS_XJ_TO_BJ ';
Where: SIDBJ.US.ORACLE.COM is the remote database GLOBAL_NAME,USERBJ is the user name of the connection SIDBJ, bj123 is the password of USERBJ,
TNS_XJ_TO_BJ is the service name of a locally established database connected to Beijing.

3. Test if the connection is successful:
SELECT * from [email protected]
If the return result is as follows, the connection is successful.
DUMMY
-----
X

4. Query The local data for the established remote connection name:
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. At this point, a dblink was established on the computer in Xinjiang to connect to the Beijing database,
Query the database in Xinjiang for information on the tables in Beijing's databases:
SELECT * from [email protected]
Query Result:
stu_id Stu_name
---------- --------------------------------------------------------------------------------
1 Zhongderong

Indicates that the query is normal.

6, about the Global_name parameter test

Create DATABASE link a connect to USERBJ identified by bj123 using ' TNS_XJ_TO_BJ ';
Set up a Dblink to execute the following query:
Select Owner,object_name from dba_objects where object_type= ' DATABASE LINK ';
You can see that a dblink has been established with the name: A.
However, when you perform the following query to retrieve remote data, an error occurs.
SELECT * from [email protected]
The reason is that because the Global_names parameter value of the local database is true, the name of the Dblink must be the same as the global_names of the remote database.
The Global_names parameter to modify the local database is false (note that modifying the local database SIDXJ is not possible by testing the parameters of the modification process, whether the Global_names parameter of the procedure is true or is not related to false)
Use the following statement to modify the parameter:
Sql> alter system set GLOBAL_NAMES=FALSE;

The following steps can be taken 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 existing Dblink

SELECT * from [email protected]--Query remote table

7, the following in the SIDXJ to establish a stored procedure, through Dblink to retrieve the remote database SIDBJ, the stored procedure 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 [email protected]';
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 test stored procedure shows that the value of the Stu_name in the process database is output.

Detailed step records for Oracle to establish Dblink