Preface
dblink is a mechanism in Oracle databases for connecting different database instances. Through dblink, users can directly query or operate tables, views, or stored procedures in another database instance in one database instance.
The role of dblink is mainly reflected in the following aspects:
- Cross-database operations: Allows users to directly access data in another database instance in one database instance.
- Simplified Data Management: Dblink allows easy management and maintenance of data distributed in multiple databases without repeating the same data operations in each database.
- Improving efficiency: Through dblink, the delay in data transmission can be reduced and the efficiency of data processing can be improved.
1. Introduction to dblink
dblink (Database Link) database link, as the name implies, is a database link. Just like a telephone line, it is a channel. When we want to access data in another database table across the local database, we must create a dblink of the remote database in the local database. Through the dblink local database, we can access the data in the remote database table like accessing the local database.
Use the CREATE DATABASE LINK statement to create a DBLINK. During the creation process, you need to specify the connection information of the target database instance, such as user name, password, database name, etc.
CREATE DATABASE LINK dblink_name CONNECT TO username IDENTIFIED BY password USING 'tns_name';
Where tns_name is the alias configured in the target database instance in the file.
Once DBLINK is created successfully, it can access objects in the target database instance. In SQL queries, you can use the syntax of @dblink_name to specify the database instance to be queryed.
SELECT * FROM table_name@dblink_name;
Example
CREATE DATABASE LINK dblink_name CONNECT TO PUTRASIT IDENTIFIED BY rasitt USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = )(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
Permissions: The account that creates the database link must have the system permissions of CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK. The account used to log in to the remote database must have the CREATE SESSION permission. Both permissions are included in the CONNECT role (CREATE PUBLIC DATABASE LINK permission is in the DBA). A public database link is available to all users in the database, while a private link is only available to the users who created it. It is impossible to authorize a private database link to another user by one user. A database link is either public or private.
2. Create dblink
There are generally two ways to create a dblink, but before creating a dblink, the user must have permission to create a dblink. Want to know about permissions for dblink to log in to the local database as sys user:
select * from user_sys_privs t where like upper('%link%');Query result set:
1 SYS CREATE DATABASE LINK NO 2 SYS DROP PUBLIC DATABASE LINK NO 3 SYS CREATE PUBLIC DATABASE LINK NO
dblink has three permissions in the database:
DATABASE LINK（CreateddblinkOnly the creator can use it，Other users can't use it） ， PUBLIC DATABASE LINK（public表示CreateddblinkAll users can use it）， PUBLIC DATABASE LINK。
Under the sys user, grant the CREATE PUBLIC DATABASE LINK and DROP PUBLIC DATABASELINK permissions to your users:
grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to scott;
Then log in to the local database as the scott user.
1) Local services have been configured
create public database link link_name connect to username identified by password using 'connect_string';
Note: link_name is the connection name and can be customized;
username is the username for logging into the database;
password is the user password for logging in to the database;
connect_string is a database connection string.
The database connection string is an alias name defined in the file in the current client database.
2) Create a link directly
create database link link_name connect to username identified by password using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = SSID) ) )';
host=the ip address of the database, service_name=ssid of the database. In fact, the two methods are similar to configuring dblink. I personally feel that the second method is better, so that it is not affected by local services. Note: If you create a global dblink, you must use systm or sys user and add public before database.
3. dblink query
To view all database links, go to System Administrator SQL> Operators, and run the command:
select * from dba_db_links;
dblink delete
DROP PUBLIC DATABASE LINK link_name;
Used by dblink
SELECT……FROM Table name@Database link name;
Querying, deleting and inserting data is the same as operating the local database, except that the table name needs to be written as "table name @dblink server".
Case: Query the emp table data in Shanghai database
select * from emp@ShangHai;
Suppose the database connection string of the Shanghai database here is Shanghai;
dblinkApplication examples
When you need to copy data across libraries and the amount of data is large, using dblink is very fast.
Case: Copying a table with a lot of data across libraries through dblink
create table test as select * from T_USER_LOG_INFO@db2
Synonyms
In the example, from emp@ShangHai can create synonyms to replace
CREATE SYNONYM Synonyms FOR Table name; CREATE SYNONYM Synonyms FOR Table name@Database link name;
Case
create synonym sh_scott_emp for emp@ShangHai;
So you can use sh_scott_emp to replace the distributed link operation with @ symbol emp@ShangHai
dblink works independently of creating the user (USERNAME of USER_DB_LINKS). Other users cannot use this connection and cannot delete it without permission.
It should be noted that in the oracle parameter, there is a parameter called global_names. If the parameter is true, then when using db link, the name of the dblink must be the same as the name of the visited database instance, otherwise an ORA-2085 error will be reported.
4. Things to note
- Permissions: The corresponding permissions are required to create and use dblink. Typically, only database administrators or users with appropriate permissions can perform these operations.
- Security: Use dblink to pay attention to security issues, especially when it comes to sensitive data or database connections across trust domains.
- Performance: Cross-base query may be affected by various factors such as network latency and database performance. Therefore, the impact of these factors on performance needs to be considered when using dblink.
In short, dblink is an important mechanism for cross-store query and data exchange in Oracle databases, and is very useful for application scenarios that require interaction between multiple database instances.
This is the article about the process and usage of Oracle database creation dblink. For more information about Oracle creation dblink and usage, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!