Dblink overview dblink is the object that defines the path from one database to another. dblink allows you to query remote tables and execute remote programs. In any distributed environment, database is necessary. In addition, dblink is a one-way connection. When dblink is created, Oracle saves the relevant dblink information in the data dictionary.
Dblink overview dblink is the object that defines the path from one database to another. dblink allows you to query remote tables and execute remote programs. In any distributed environment, database is necessary. Note that db link is a one-way connection. When dblink is created, Oracle saves the relevant dblink information in the data dictionary.
Dblink Overview
Dblink is the object that defines the path from one database to another. dblink allows you to query remote tables and execute remote programs. In any distributed environment, database is necessary. Note that db link is a one-way connection.
When dblink is created, Oracle stores the relevant dblink information in the data dictionary. When dblink is used, oracle accesses the corresponding remote database through the user-defined connection information through Oracle Net to complete the corresponding work.
Items to be confirmed before dblink is established:
Check whether the connection from the local database to the remote database can be accessed through the listener -- sqlplus aa/bb @ cc is normal, and confirm that the user in use has the corresponding access permission on the remote database.
Query on the source database:
SYS @ bys3> select * from user_sys_privs where privilege like upper ('% link % ');
USERNAME PRIVILEGE ADM
-------------------------------------------------------------------------
SYS DROP PUBLIC DATABASE LINK NO
SYS CREATE DATABASE LINK NO
SYS CREATE PUBLIC DATABASE LINK NO
[Oracle @ bys3 admin] $ cat tnsnames. ora
Ocm =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.215) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ocm1)
)
)
Data in the target database table:
BYS @ ocm1> select * from test;
DEPTNO DNAME LOC
------------------------------
10 chedan zheng
20 chifan bj
####################################### Experiment
1. create a public DBLINK: --- the public parameter must be used. The OWNER is publicSYS @ bys3> create public database link ocm1 connect to bys identified by bys using 'occm ';
Database link created.
Elapsed: 00:00:00. 03 --- if the syntax is normal and the using network service name is normal, the statement is completed quickly. If it takes a long time to complete, it is likely that there is a problem with the configuration of the network service name.
SYS @ bys3> select * from dept @ ocm1; --- the DEPT table does not exist.
Select * from dept @ ocm1
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from OCM1
SYS @ bys3> select * from test @ ocm1;
DEPTNO DNAME LOC
------------------------------
10 chedan zheng
20 chifan bj
SYS @ bys3> drop public database link ocm1; -- delete PUBLIC DBLINK
Database link dropped.
Another statement for creating a data link: the name of the data link can be determined by yourself-but it is best to set the target database information that can mark the connection
SYS @ bys3> create public database link aa connect to bys identified by bys using 'occm ';
Database link created.
Elapsed: 00:00:00. 01
SYS @ bys3> select * from test @ aa;
DEPTNO DNAME LOC
------------------------------
10 chedan zheng
20 chifan bj
############
2. create a private DBLINK --- the user who creates the dblink owns the dblinkTEST1 @ bys3> create database link ocm2 connect to bys identified by bys using 'occm ';
Database link created.
TEST1 @ bys3> select * from test @ ocm2;
DEPTNO DNAME LOC
------------------------------
10 chedan zheng
20 chifan bj
TEST1 @ bys3> select db_link from dba_db_links;
DB_LINK
Bytes ----------------------------------------------------------------------------------------------------
AA
OCM2
TEST1 @ bys3> drop database link ocm2;
Database link dropped.
3. use the connection string of the network service to create the DBLINK. In this way, the ntnsnames is not used. influence of Information in the ora file TEST1 @ bys3> create database link ocm4 connect to bys identified by bys using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.215) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ocm1)
)
)';
Database link created.
Elapsed: 00:00:00. 01 --- this long command should be written in the text and copied and executed directly.
TEST1 @ bys3> select * from test @ ocm4;
DEPTNO DNAME LOC
------------------------------
10 chedan zheng
20 chifan bj
TEST1 @ bys3> select db_link from dba_db_links;
DB_LINK
---------------------------
AA
OCM2
OCM4
4. ORA-02011: Error -- because the data link name already exists
TEST1 @ bys3> create database link ocm2 connect to bys identified by bys using 'occm ';
Create database link ocm2 connect to bys identified by bys using 'occm'
*
ERROR at line 1:
ORA-02011: duplicate database link name
TEST1 @ bys3> select db_link from dba_db_links;
DB_LINK
Bytes ----------------------------------------------------------------------------------------------------
AA
OCM2
