Question on DBLink for 2 databases oracle

Question

I have a server with 2 databases: dbtest and checkdb. I need that user user_b of dbtest can made a select on a table of other database. How i have to set the dblink?

Answer 1

On the top of your script run

alter session set global_names=false;

then whenever you need to fetch data from checkdb you add the link as a suffix. don't know exactly how your dbs are defined but something like

user_b is on dbtest

select * from table_name@dblink 
#might be that dblink is not the exact name. 

hope it helps.

Answer 2

Connected as user in one of those databases, create a database link to another database following the syntax (https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CREATE-DATABASE-LINK.html#GUID-D966642A-B19E-449D-9968-1121AF06D793). It hasn't changed for ages, I suppose.

For example:

SQL> create database link dbl_scott     -- database link name
  2    connect to scott                 -- you're connecting to this user (scott) ...
  3    identified by tiger              -- ... which is identified by this password (tiger)
  4    using 'db11g:1521/orcl';         -- using clause has a database server (db11g):port (1521)/service name (orcl)

Database link created.

SQL> -- Testing; it has to return a row
SQL> select * From dual@dbl_scott; 

D
-
X

SQL>

The USING clause could be shortened by putting target database alias (the one written in TNSNAMES.ORA file on your database server). If you don't have access to that file (as you aren't a DBA), then the above option works OK.

Note that - even if you use invalid settings - database link might be created, but it won't work. For example:

SQL> drop database link dbl_scott;

Database link dropped.

SQL> create database link dbl_scott
  2    connect to xyzalksfjlaskfj
  3    identified by abc
  4    using '9809803242';

Database link created.

SQL> select * from dual@dbl_scott;
select * from dual@dbl_scott
                   *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>

Therefore, be careful.