{"id":3237,"date":"2021-07-21T08:00:00","date_gmt":"2021-07-21T05:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3237"},"modified":"2023-05-05T19:16:55","modified_gmt":"2023-05-05T16:16:55","slug":"pos-mporoyme-na-echoyme-apomakrysmeni","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-echoyme-apomakrysmeni\/","title":{"rendered":"How can we access another oracle database remotely"},"content":{"rendered":"

In this article we will analyze how we can access tables \/ views located in a remote database Oracle<\/strong>. To achieve this we need to create an object called Database Link<\/strong>. <\/p>\n\n\n\n

Using Database Link<\/strong> we can call tables and views located in another database in queries by adding at the end \u201c@<\/strong>\u201d and the name of the Database Link. In addition to SELECT, we can also do INSERT, UPDATE, DELETE on these tables.<\/p>\n\n\n\n

<\/p>\n\n\n\n

The preparation <\/h2>\n\n\n\n

First we need to go to the folder where the file is located tnsnames.ora<\/strong> on our local server. This file contains the network settings required for a user to connect to the Oracle database.<\/p>\n\n\n\n

cd $ORACLE_HOME\/network\/admin<\/code><\/pre>\n\n\n\n
\"\"
01<\/figcaption><\/figure>\n\n\n\n
By editing the file, under the record for our current database (ORCL) we should also add a record for the remote one we want to connect to (REMOTEDB). There we should define Host<\/strong>,Port <\/strong>and as Service_name <\/strong>the name of the base \/ service we want to connect to.<\/p>\n\n\n\n
*Because for the example I didn't have another instance I put a record with the same one.<\/em><\/p>\n\n\n\n
vi $ORACLE_HOME\/network\/admin\/tnsnames.ora<\/code><\/pre>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
In the remote base, we should have created a user who will have the right to connect, along with any other right we want to see specific objects.<\/p>\n\n\n\n
In our case we will give select permission to only one table:<\/p>\n\n\n\n
-- Create the user \ncreate user dblink_user\n  identified by password\n  default tablespace USERS\n  temporary tablespace TEMP\n  profile DEFAULT;\n-- Grant\/Revoke role privileges \ngrant connect to dblink_user;\ngrant resource to dblink_user;\n\ngrant select on scott.dept to dblink_user;<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Create Public Database Link<\/h2>\n\n\n\n
In our local base we will create the Public Database Link<\/strong>. The Public<\/strong> means that it can be used by any user of the database.<\/p>\n\n\n\n
It will be needed at connect <\/strong>to set the credentials of the user we made before on the remote base and on using <\/strong>the registration to tnsnames.ora<\/strong> which we added:<\/p>\n\n\n\n
create public database link REMOTEDB\nconnect to dblink_user identified by password\nusing 'REMOTEDB'; \n--drop public database link REMOTEDB;<\/pre>\n\n\n\n
If we select the view dba_db_links<\/strong>, we will see the database link we made:<\/p>\n\n\n\n
select * from dba_db_links;<\/pre>\n\n\n\n
\"\"
03<\/figcaption><\/figure>\n\n\n\n
We are ready to test the query on the table, all that is needed is after the name of the table to add "@" the name of the database link:<\/p>\n\n\n\n
select * from scott.dept@REMOTEDB;<\/pre>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
If we try to access another table that the remote user we made the database link to (dblink_user) does not have access to, then it will return an error:<\/p>\n\n\n\n
select * from scott.bonus@REMOTEDB;<\/pre>\n\n\n\n
\"\"
05<\/figcaption><\/figure>\n\n\n\n
<\/p>\n\n\n\n
Create Private Database Link<\/h2>\n\n\n\n
However, since it is not safe for any user in a database to have access to a database link, we can create a database link that only works for a specific user.<\/p>\n\n\n\n
For a start we should give the right CREATE DATABASE LINK<\/strong> to the user we want to use the database link:<\/p>\n\n\n\n
grant CREATE DATABASE LINK to stratos;<\/pre>\n\n\n\n
Then we connect as this user (e.g. connect stratos\/password) and create the database link as before without the word PUBLIC:<\/p>\n\n\n\n
create database link REMOTEDB_FROM_STRATOS\nconnect to dblink_user identified by password\nusing 'REMOTEDB'; \n--drop database link REMOTEDB_FROM_STRATOS<\/pre>\n\n\n\n
If we now select the following view again with sysdba <\/strong>user we will see that we have two database links, one belongs to PUBLIC <\/strong>and the other to the user we created:<\/p>\n\n\n\n
select * from dba_db_links;<\/pre>\n\n\n\n
\"\"
06<\/figcaption><\/figure>\n\n\n\n
So connected as this user if we run the query with his own database link it will work normally:<\/p>\n\n\n\n
select * from scott.dept@REMOTEDB_FROM_STRATOS;<\/pre>\n\n\n\n
\"\"
07<\/figcaption><\/figure>\n\n\n\n
If we try to run the same query as any other user, we will get the error with the following message:<\/p>\n\n\n\n
\"\"
08<\/figcaption><\/figure>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n