What does remote database have to do for DB link in Oracle

dblinkoracleoracle-11g

In local database, it has to create DB LINK through CREATE DATABASE LINK.

  1. What about remote database side, what does it have to do?

  2. On fixed user link, local database need to know ID and password set.
    Is the ID is a usual account with which someone can access to the remote database without DB LINK or the ID is only for DB LINK?

Best Answer

What about remote database side, what does it have to do?

There must have Oracle Net installed on both local and remote database servers. And the user being used should have CREATE SESSION system privilege on the remote database.

On fixed user link, local database need to know ID and password set. Is the ID is a usual account with which someone can access to the remote database without DB LINK or the ID is only for DB LINK?

You can have to options to connect to the remote database, one is using current user and another is fixed user which can be defined explicitly after CONNECT TO clause. That user is a remote user which must be exist on the remote database server and must have CREATE SESSION privilege . If you omit this then currently logged in user is used to access tables on the remote server. 

[oracle@localhost admin]$ sqlplus scott/scott

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 30 16:39:08 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create database link dblink1 using 'account';

Database link created.

SQL> create database link dblink2 connect to jay identified by jay using 'account';

Database link created.

SQL> select * from emp@dblink1;
select * from emp@dblink1
                  *
ERROR at line 1:
ORA-28000: the account is locked
ORA-02063: preceding line from DBLINK1


SQL>

Look at the above example. I have connected to the local database as SCOTT user and create two dblinks to connect to the remote server called 'account' using 'account' TNSNAME.

First dblink dblink1 uses current user which is SCOTT in our case is used in order to select * from emp at the remote server 'account'. In the example the select statement has returned an error stating that the SCOTT user at the remote site is locked.

The second dblink dblink1 uses the fixed user called jay hence this dblink can be used to access tables owned by Jay schema as well as other tables according to the privileges the user has got.

For details please refer to the Oracle Documentation. CREATE DATABASE LINK

Related Question