Oracle Database Link and Hibernate Mapping

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

SQL> GRANT CREATE PUBLIC DATABASE LINK TO <USER>;

SQL> GRANT DROP PUBLIC DATABASE LINK TO <USER>;

SQL> GRANT CREATE VIEW TO <USER>;

Database Link

Login and run below SQL to create a database link 

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

xxxxxxxxxx

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

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

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

Delete Database Link

DROP <PUBLIC> DATABASE LINK <DB_LINK>;

View

Create View to map remote database table

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

Hibernate Mapping

We can use a common way to create hibernate mapping to a Database View. For example

<hibernate-mapping>
<class name="com.xx.xxx.xxxx.hibernate.entity.view.XXXX"
table="<VIEW>" dynamic-update="true">
...
</class>
</hibernate-mapping>

SYNONYM

We also can create a synonym table instead of view after the Database link created

CREATE PUBLIC SYNONYM <SYNONYM>  FOR  <REMOTE TABLE>@<DB_LINK>;

Hibernate Mapping

We can map a synonym table like below

xxxxxxxxxx

<hibernate-mapping>

    <class name="com.xx.xxx.xxx.hibernate.entity.view.XXXX"

           table="<Remote Table>@<DB_LINK>" dynamic-update="true">

           ...

    </class>

</hibernate-mapping>

Furthermore,  hibernate configuration need below changes

<!-- Add below one -->
<prop key="hibernate.synonyms">true</prop>

And hibernate need disable auto validation.

<!-- Remove below one -->
<!--prop key="hibernate.hbm2ddl.auto">validate</prop-->