The article is to describe how to create a Database link in Oracle and use Hibernate to create mapping for the remote table.

Grant Privilege

Grant create/drop database link privilege to a user, and we also need the grant to create view privilege for the user

Run below SQL by SYSDBA

1

SQL&gt; GRANT CREATE PUBLIC DATABASE LINK TO &lt;USER&gt;;<br><br>SQL&gt; GRANT DROP PUBLIC DATABASE LINK TO &lt;USER&gt;;<br><br>SQL&gt; GRANT CREATE VIEW TO &lt;USER&gt;;

Database Link

Login and run below SQL to create a database link

Java

1

CREATE <PUBLIC> DATABASE LINK <DB_LINK> CONNECT TO <DATABASE_USER> IDENTIFIED BY <DATABASE_PASSWORD> USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <DATABASE HOSTNAME>)(PORT = <PORT>)))(CONNECT_DATA = (SERVICE_NAME = <SERVICE NAME>)))';

Attached Oracle document:  https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm

Viewing Information About Database Links

Java

1

SELECT * FROM DBA_DB_LINKS  #Lists all database links in the database.

2

3

SELECT * FROM ALL_DB_LINKS #Lists all database links accessible to the connected user.

4

5

SELECT * FROM USER_DB_LINKS  #Lists all database links owned by the connected user.

Delete Database Link

1

DROP <PUBLIC> DATABASE LINK <DB_LINK>;

View

Create View to map remote database table

1

CREATE VIEW <VIEW> AS (SELECT * FROM <REMOTE TABLE>@<DB_LINK>;

Hibernate Mapping

#database #tutorial #oracle #hibernate 5 #database links