In this article I'll describe how to create to remote Oracle database, because there are no clear description for that. There are two ways to achieve it: throught link, i.e. you will be able to execute query like
select * from table@db or directly connecting to remote database with sqlplus command
This article is for Windows Server 2012, but it could works with other Windows versions and with Linux, if you could locate files on your own.
Note: even if you want to create link
@db for SQL you should follow both steps, because links are using SID from
Step 1 - create sqlplus connection
First of all you'll need to create record in your
tnsnames.ora located in
PathToOracle\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN and you should add connection string:
db = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db) ) )
Host could be localhost or ip address of the remote database. Now you just type
sqlplus l/p@db... and recieve error ORA-12638.
You should open file
sqlnet.ora in the same directory as
tnsnames and comment line:
#SQLNET.AUTHENTICATION_SERVICES= (NTS). This fix error for me, but this error could be caused be many reasons.
Step 2 - Creating link
But I need to connect to database and its tables from sql console of the IDEA or SQL Developer, but I've recievening error ORA-02019. After some googling I found that I should create link. It's obvious for expirienced Oracle SQL developers, but I'm not DBA and have low expirience, so I've forgot about it.
Following command in sqlplus will cteate link:
create public database link LINK_NAME connect to SCHEMA identified by PASSWORD using 'SID';
If you've done mistake, then you could drop link using following:
drop public database link LINK_NAME';
If you create link using
public keyword then you should delete it using it too. I don't know why so strict policy for commands, but I took it for granted.