Oracle database link, oracledatabase

Oracle database link, oracledatabase

Database link, which is often called db link.
Role: a method used to access data from database a to database B. Similar to table synchronization in ogg (User synchronization ). As mentioned above, db link is mainly used for cross-database queries. Many services require cross-database queries. In our company, sometimes we have to create some db links.
Advantages: The creation method is simple, easy to understand, and can have all permissions for creating users.
Disadvantages: Naturally, convenient means will have more disadvantages.
1. A large amount of database resources are consumed. Each time the local system connects to the remote system through DBLINK, a local session is generated. If the local session does not exit or is manually released, it can be automatically released only when the session times out, A large amount of system resources will be wasted.
2 prone to database bugs: a large number of use of DBLINK, local systems are prone to ORA-02068, ORA-03113, ORA-02080, ORA-02054, ORA-02050 and other Oracle errors, this error will affect most of the current session is being processed business
3. Poor performance: due to the use of DBLINK, the local system performs a full table scan on the remote database each time it acquires data, and all data is transmitted back to the local database, this results in lower performance and serious waste of current system resources. In particular, an SQL statement is called "performance killer", especially for OLTP databases, to fetch data from two dblinks.
4. Unstable data transmission, serious bandwidth occupation, and risk of packet loss: DBLINK itself does not have data storage, monitoring, and other functions. It is transmitted through databases on the network, if data packet loss occurs during transmission, DBLINK will not discover it. Only packet loss can be found after the service is completed, which will affect the services currently being processed. In addition, data queried through DBLINK is transmitted to the local database like all data. The data transmission volume is large and the bandwidth usage is serious, which may cause network congestion.
5 restrictions on Oracle Data functions: DBLINK itself does not support operations on large oracle LOB objects. errors may occur when you use PROCDURE or COMMIT.
6. Poor Scalability: if multiple systems use DBLINK for transmission, one or more DBLINK links or corresponding interface programs will be added for each added system. If one of the interfaces is modified, interface programs in all associated systems need to be modified
7. Poor maintainability and low security: the remote database username, password, and IP address must be stored in the local database for connection through DBLINK. For example, if the remote database is used for password modification or IP address change, the local database also needs to be modified, if there are many systems, each system must be modified at the same time. The local database administrator can view the passwords and IP addresses of remote data, which affects system security.
However, even if there are so many shortcomings, some people will still use them. In the final analysis, I personally feel that oracle is not particularly good at the interaction between the two databases, ogg is easy to use, but we suffer from many tables.

Usage: the prerequisite for creating dblink is that the two databases must be able to interact with the remote database network. Configure the parameters for accessing the target in the tnsname file of the source database. And enable tnsping.
Database link can be divided into two types: public and private ). As the name suggests, public dblink is available to all users in the source database, while personal dblink is available only to Creator users.
You must have the relevant permissions to create a dblink.
Select * from user_sys_privs where privilege like upper ('% LINK % ');
USERNAME PRIVILEGE ADM
-------------------------------------------------------------------------
SYS CREATE DATABASE LINK NO
SYS DROP PUBLIC DATABASE LINK NO
SYS CREATE PUBLIC DATABASE LINK NO
The create database link permission is used to CREATE a personal dblink.
Create public database link is the permission to CREATE a PUBLIC dblink.

Create dblink
When tnsnames has parameters. With the corresponding permissions, you can create dblink. You can create one of the following methods.

The statement for creating a personal dblink starts with CREATE database link:
Use the username and password of the current user to connect to the remote database. If the password is incorrect, an error is reported.
Create database link test1 using 'test ';

Create public database link
Create a public dblink using the specified user and password
Create public database link test3 connect to scott identified by tiger using 'test ';

Create a shared database connection
Create shared public database link test4 connect to scott identified by "tiger" authenticated by username identified by "passwd" using 'test ';
The shared database link Method limits the number of connections from the database to the remote database, so as to avoid too much pressure on the remote database caused by too many connections.

Another method is to create a table without using tnsname.
CREATE database link link_name
Connect to user identified by screct
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = ip) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = sales)
)
)';

Delete dblink
First, you need to find out which dblinks are available.
Select owner, object_name from dba_objects where object_type = 'database link ';
If pubilc is used, the deletion method is drop public database link.
If it is an individual, the deletion method is drop database link.

In essence, dblink is not very complicated. However, given the impact on the database. In line with the principle of less use and less use. If you can replace dblink with some import/export operations (we are doing the same ). Is the best.