Use Oracle DBLink to access objects between databases.

Use Oracle DBLink to access objects between databases.

Oracle comes with the DBLink function. It is used to logically think of multiple oracle databases as one database, that is, one database can operate on objects in another database, for example, if we create a data database1, We need to operate the tables in database database2, or we need to operate the tables in database database3 on a remote machine, we can use the powerful function of dblink!

1. If we want to create a global DBLink, that is, no matter what role can be used, we need to first determine whether the user has the DBLink permission. If not, we need to use the sysdba role to authorize the user:

Check that the user has the DBLink permission:

select * from user_sys_privs where privilege like upper('%DATABASE LINK%');  

No, sysdba authorization is used:

grant create public database link to dbusername; 

2. Use a statement to create a DBLink:

Create database link the dblink name to be created connect to the user name identified by the password using to connect to the database (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = IP address of the connected database HOST) (PORT = PORT number) (CONNECT_DATA = (SERVICE_NAME = Name of the connected Database Service )))';

If you create a global dblink, you must use the mongom or sys user and add public before the database:

create public database....

For example, create test_dblink in testdb2 to operate the testdb1 database in host 192.168.1.254:

create database link tset_dblinkconnect to username identified by password1using '(DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.254)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = testdb1)    ))';

3. Use DBLink:

Query the data in another database. Other modifications and deletions are the same. They are the names of other database tables @ dblink name created in this database:

Select xxx FROM table name @ dblink name;

For example, we use dblink in testdb2 to view data in the tb_user table in testdb1:

select * from tb_user@tset_dblink;

View the dblink created in the data:

select owner,object_name from dba_objects where object_type='DATABASE LINK';select * from dba_db_links;

Delete the corresponding dblink:

Drop database link dblink name;

Disable dblink connection:

alter session close database link 'dblink_name'

Create and delete views:

Create or replace view name as (select field from user. Table Name @ dblink1); drop view name;

Oracle dblink problems, urgent, etc. Database A and database B are mutually built with dblink which can be accessed from each other, but the lead is not allowed to change the database

Check the oracle listening configuration, modify the password of database B, delete the original dblink, and then recreate DBLINK.

How to use oracle database dblink

Create a public database link
Create public database link "database link alias"
Connect to "Login User" identified by "Login Password"
USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = IP) (PORT = 1521) (CONNECT_DATA = (SID = SID )))';
For example:
CREATE PUBLIC DATABASE LINK MYDBLINK
Connect to USER1 identified by PASSWORD1

USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.10.10.10) (PORT = 1521) (CONNECT_DATA = (SID = ORA10 )))';