Use synonyms in Oracle

I. background

There are two sid: Asid and Bsid. There are two users Auser1 and Auser2 under Asid, and there is a user Buser1 under Bsid. Among them, Auser2 and Buser1 establish dblink. You want to log on to Buser1 to access three tables table1, table2, table3, and function function1 on auser1.

Step 2

① Log on to Auser1 and authorize Auser2 (add delete if delete is required)

Grant insert, select, update on table1 to Auser2;

Grant insert, select, update on table2 to Auser2;

Grant insert, select, update on table3 to Auser2;

Grant execute on function1 to Auser2;

② Log on to Auser2 to create a synonym

Create synonym table1 for Auser1.table1;

Create synonym table2 for Auser1.table1;

Create synonym table3 for Auser1.table1;

Create synonym function1 for Auser1.function1;

③ Log on to Buser1 to find the db_link name (for example, the name of the detected db_link is mydblink1)

Select username, db_link from user_db_links

④ Create a synonym for Buser1

Create synonym table1 for Auser2.table1 @ mydblink1;

Create synonym table2 for Auser2.table2 @ mydblink1;

Create synonym table3 for Auser2.table3 @ mydblink1;

Create synonym function1 for Auser2.function1 @ mydblink1;

⑤ Test

Log on to Buser1 and use select * from table1 to access table1.

Iii. Notes

You can also create a synonym for procedure, but commit is not allowed during such execution. If you want to submit, please commit () in JDBC, for example ().