Oracle DB_LINK, oracledb_link

Oracle DB_LINK, oracledb_link

1. Create a remote database service name first (Note: if the server has both an oracle server and a client, you must configure the service name in tnsnames. ora of the server; otherwise, the following error is reported ):

SQL> select count (*) from webxxxt_pic.xxx @ pic_link; select count (*) from webxxxt_pic.xxx @ pic_link * row 1st error: ORA-12154: TNS: Unable to parse the specified connection identifier

2. Grant DB_LINK creation permission:

grant CREATE PUBLIC DATABASE LINK，DROP PUBLIC DATABASE LINK to scott;

Check which users have the permission to create DB_LINK:

select * from user_sys_privs t where t.privilege like upper('%link%');

3. Create DB_LINK with the authorized user:

create database link DBLINK_NAME connect to USER01 identified by PASSWORD using 'TNS_NAME';

DBLINK_NAME: name of DB_LINK
USER01: Remote database account
PASSWORD: Account of the remote database
TNS_NAME: Remote database service name

4. view all the db_links in oracle:

select owner,db_link,username from dba_db_links;

5. Use DB_LINK:

select * from scott.tb_test@DBLINK_NAME;
insert into scott.tb select * from scott.tb@pic_link;