Oracle Database Remote Access through DBLINK

Oracle Database Remote Access through DBLINK

What is DBLINK?

A Database Link is a Database Link, just like a telephone line. It is a channel. When we want to access data in another Database table across local databases, you must create a dblink for a remote database in the local database. You can use the dblink local database to access the data in the remote database table just like accessing the local database.

How to Use DBLINK?

Scenario: if the current database user is ALANLEE, you need to use ALANLEE to collect data from the remote database.

The remote database information is as follows:

HSAJ216 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 12.1.3.216) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = hscsserver)
)
)

Remote database username: hs_user, password: hundsun

Step 1: Check whether the user has the permission to create a database link

-- Check whether the ALANLEE user has the permission to create the database link.
Select * from user_sys_privs where privilege like upper ('% database link %') and username = 'alance ';
Select * from user_sys_privs t where t. privilege like upper ('% link % ');

Dblink has such permissions in the database. For example, create database link indicates that the created dblink can only be used by the creator, but cannot be used by other users. create public database link indicates that all users of the created dblink can use it, drop public database link indicates the permission to delete a PUBLIC dblink.

If the relevant data is found, the ALANLEE user has the relevant permissions. If the data is not found, the ALANLEE user does not have the relevant permissions.

Step 2: If the user does not have the corresponding permissions, the user must be authorized. If the ALANLEE user has the permissions similar to that authorized by the Administrator, the user can be directly authorized by the current user, if ALANLEE does not have such permissions, use database users with higher permissions such as SYS/SYSTEM to authorize ALANLEE users.

-- Grant the ALANLEE user the permission to create a database link.
Grant create public database link to ALANLEE;
-- Grant the ALANLEE user the permission to delete the database link.
Grant drop public database link to ALANLEE;

Here we use the public dblink, that is, the dblink that all users can use. You can grant the corresponding permissions as needed, after successful authorization, you can view whether the authorization is successful in the first step.

Step 3: Create a remote database link through the ALANLEE user (Database address: 12.1.3.216 Username: hs_user password: hundsun)

Drop public database link HSAJ216;
Create public database link HSAJ216
Connect to hs_user identified by hundsun
Using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 12.1.3.216) (PORT = 1521 )))
(CONNECT_DATA = (SERVICE_NAME = hscsserver )))';

To avoid other problems, we recommend that you use the global listening Instance name of the remote database as the database link name, that is, HSAJ216 shown in the remote database information.

Step 4: query the created remote connection of the database

Select owner, object_name from dba_objects where object_type = 'database link ';

If you have created your own database link data, the data is successfully created. Otherwise, the data does not exist.

Step 5: test the established remote database connection

Select * from dual @ HSAJ216;

If something can be found, remote access is successful.

How can I query data in a table of a user in a remote database? The SQL statement is as follows:

Select * from hs_asset.client @ HSAJ216;

If you can query the table data, you can start data collection, query the corresponding data, and insert it into the table of the local database.

Last step: collect data from the remote database through the stored procedure and insert it into the local database

/**
* Synchronize customer data from the counter to a temporary table
*/
Create or replace PROCEDURE SP_SYNC_CUSTOMER_TEMP (UPDATE_TOTAL out number) IS
INDEX_COUNT NUMBER;
INDEX_TOTAL NUMBER;
CURSOR CR IS
Select a. client_id, -- customer ID
A. branch_no, -- Branch
A. id_no, -- ID number
A. client_name, -- CUSTOMER NAME
A. client_status, -- customer status
A. open_date, -- account opening date
C. fund_account, -- fund account
C. main_flag, -- primary account ID
C. asset_prop, -- asset attributes
B. birthday, -- birthday date
B. address, -- address
B. home_tel, -- home phone number
B. e_mail, -- email
B. fax, -- fax
B. mobile_tel, -- mobile phone number
B. office_tel, -- Organization phone number
B .zip code, -- zip code
B. account_data -- account opening standard information
From hs_asset.client @ HSAJ216
Inner join (select client_id,
Birthday,
Address,
Home_tel,
E_mail,
Fax,
Mobile_tel,
Office_tel,
Zipcode,
Account_data
From hs_asset.clientinfo @ HSAJ216
Union all
Select client_id,
'20140901' as birthday,
Address,
Contact_tel as home_tel,
E_mail,
Fax,
Mobile_tel,
Contact_tel as office_tel,
Zipcode,
'A' as account_data
From hs_asset.organinfo @ HSAJ216) B
On a. client_id = B. client_id
Inner join hs_asset.fundaccount @ HSAJ216 c
On a. client_id = c. client_id
Where c. asset_prop = '0 ';
BEGIN
Insert into t_coll_result (id, CREATE_DATE, REMARK)
Values (seq_t_coll_result_id.nextval, sysdate, 'start-run [SP_SYNC_CUSTOMER_TEMP] to clear the temporary table TEMP_SYNC_CUSTOMER ...');
Execute immediate 'truncate TABLE TEMP_SYNC_CUSTOMER ';
Insert into t_coll_result (id, CREATE_DATE, REMARK)
Values (seq_t_coll_result_id.nextval, sysdate, 'over-[SP_SYNC_CUSTOMER_TEMP] clearing the client synchronization temporary table TEMP_SYNC_CUSTOMER completed ...');

Insert into t_coll_result (id, CREATE_DATE, REMARK)
Values (seq_t_coll_result_id.nextval, sysdate, 'start-now execute [SP_SYNC_CUSTOMER_TEMP] to synchronize temporary table import data to the customer TEMP_SYNC_CUSTOMER ...');
Commit;

INDEX_COUNT: = 1;
INDEX_TOTAL: = 0;
FOR C IN CR LOOP
-- Customer temporary table
Insert into TEMP_SYNC_CUSTOMER
(CODE,
ORGA_ID,
ID_CARD,
NAME,
CLOSE_STATUS,
ACCOUNT_CREATE_DATE,
CAPITAL_ACCOUNT,
BIRTHDAY,
ADDRESS,
TEL,
BINDING_EMAIL,
BINDING_MOBILE,
MAIN_FLAG
)
VALUES
(C. client_id,
C. branch_no,
C. id_no,
C. client_name,
C. client_status,
C. open_date,
C. fund_account,
C. birthday,
C. address,
C. home_tel,
C. e_mail,
C. mobile_tel,
C. main_flag );

INDEX_COUNT: = (INDEX_COUNT + 1 );
INDEX_TOTAL: = (INDEX_TOTAL + 1 );

IF INDEX_COUNT> 100000 THEN
COMMIT;
Insert into t_coll_result (id, CREATE_DATE, REMARK)
Values (seq_t_coll_result_id.nextval, sysdate, '[SP_SYNC_CUSTOMER_TEMP] has been imported to TEMP_SYNC_CUSTOMER' | INDEX_TOTAL | 'data records ...');
Commit;
INDEX_COUNT: = 1;
End if;
End loop;

Insert into t_coll_result (id, CREATE_DATE, REMARK)
Values (seq_t_coll_result_id.nextval, sysdate, 'over-[SP_SYNC_CUSTOMER_TEMP] synchronization of the customer's temporary table TEMP_SYNC_CUSTOMER has been completed, a total of imports '| INDEX_TOTAL | 'items ...');
UPDATE_TOTAL: = INDEX_TOTAL;
COMMIT;
END SP_SYNC_CUSTOMER_TEMP;

Of course, it is impossible for us to manually execute SQL every time. Therefore, we can use the scheduled tasks of the Oracle database to automatically execute the stored procedures we write at a certain time every day, this is relatively perfect.

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151426.htm