Cross-database Oracle operations and use of dblink

I helped a friend with Oracle cross-database operations a few days ago and shared the specific operation process:

1. database with different IP addresses (dblink)

Local Database IP address 10.56.7.26

Remote database IP address 233.215.219.8

1. In the Database Installation File 10.56.7.26, find the $ ORACLE_HOME/Network/admin/tnsnames. ora file,

 

Add at the end

 

Mediadblink =

(Description =

(Address_list =

(Address = (Protocol = TCP) (host = 233.215.219.8) (Port = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

 

2. log on to the 10.56.7.26 database and create a database link. You must use the sysdba role to log on and assign permissions to the database.

 

Run the following SQL statement:

 

Create public database link mediadb

 

Connect to smsuser identified by zonefree2better

 

Using 'mediadblink ';

After creation, use it to check whether it is successful:

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

 

3. Create a trigger in 10.56.7.26

Insert the remote data table SQL into the trigger:

Create or replace trigger t_t_test

After insert on t_sta_bill

For each row

Declare

-- Local variables here

Begin

-- Operate a remote data table

Insert into t_test @ mediadb

(Userid, username, password)

Values (: New. userid,: New. username,: New. Password );

End t_t_test;

 

2. Different users on the same machine can use different databases on one machine.

Select ssh2.a. * From ssh2.a, orcl. A where ssh2.a. user_id = orcl. A. user_id SSH2, orcl as the user, and a as the data table