Oracle Database Remote Access through DBLINK, oracledblink

Oracle Database Remote Access through DBLINK, oracledblink

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 ALANLEE users have the permission to CREATE database link select * from user_sys_privs where privilege like upper ('% database link %') and username = 'alanlil '; 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 permission to delete a 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 HSAJ216connect to hs_user identified by hundsunusing '(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 the 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. client_id, -- customer ID. branch_no, -- Branch. id_no, -- ID number. client_name, -- customer name. client_status, -- customer status. open_date, -- account opening date c. fund_account, -- Capital Account c. main_flag, -- primary account ID c. asset_prop, -- asset property B. birthday, -- birthday date B. address, -- address B. home_tel, -- home phone number B. e_mail, -- mailbox B. fax, -- fax B. mobile_tel, -- mobile phone number B. office_tel, -- Organization phone B .zip code, -- zip code B. account_data -- account opening specifications from hs_asset.client @ HSAJ216 a 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, '000000' 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. client_id = B. client_id inner join hs_asset.fundaccount @ HSAJ216 c on. 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-execute [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 temporary table TEMP_SYNC_CUSTOMER for synchronization has been completed... '); insert into t_coll_result (id, CREATE_DATE, REMARK) values (values, 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 -- 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 entries... '); 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 to the customer's temporary table TEMP_SYNC_CUSTOMER has been completed. A total of '| INDEX_TOTAL |' data entries... '); 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.

 

Conclusion: In order not to leave regret or regret in our life, we should seize every opportunity to change our life.

 

Alimail: AlanLee

Blog: http://www.cnblogs.com/AlanLee

This article is from the blog site. You are welcome to join the blog site.