How to create database link in oracle

What is database link?

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. 

How to create a database link in Oracle? 

To create database link you must specify the database link name, the remote user to connect to, the password for the remote user and the TNS service name for the database link connection. Below are the command to create database link in oracle.

create public database link mylink connect to remote_username identified by mypassword using 'tns_service_name';

How to access object in database link

To access object in database link you can use @link_name as query below :

SELECT * FROM employees@local;

for more information about oracle database link, you can find in oracle docs page. 

 

How to set ORACLE_HOME in Windows Environment Variable

What is ORACLE_HOME used for?

The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory.

What is ORACLE_BASE used for?

* The ORACLE_BASE is also an environment variable to define the base/root level directory where you will have the Oracle Database directory tree - ORACLE_HOME defined under the ORACLE_BASE directory. 

How to set ORACLE_HOME in Environment variable

To set the ORACLE_HOME environment variable go to My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete.

oracle home environment variable

After setting the environment variables, open a fresh CMD tool and check whether they set properly or not. Do not try on already opened CMD tool to make sure the variables set or not.

How to check if ORACLE_HOME is set

Open cmd and type this command

C:\echo %ORACLE_HOME% 

How to resolve error TNS:listener does not currently know of service requested in connect descriptor

This time we would like to look at one of problem in oracle, especially “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”.

The vast majority of the time, this error results from an incorrectly specified connect descriptor in the tnsnames.ora file. Let’s look at a very typical example then diagnose and fix it. After that we will dig in to how the listener comes to know of a service name.

For the first we need to analyze why the error occured by starting listener.

[oracle@vmlnx01 admin]$ lsnrctl start
 
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 18:44:49
 
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
 
Starting /ora00/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Log messages written to /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                16-MAR-2011 18:44:49
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
The listener supports no services
The command completed successfully

It said that The listener supports no services. It means that the listener doesn't have any associates service. So the solution is we need to add a service to listener.ora as example below :

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME=vlnxora1)
      (ORACLE_HOME = /ora00/app/oracle/product/10.2.0/db_1)
      (SID_NAME = vlnxora1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01.vmdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

Restart listener by command lsnrctl stop followed by lsnrctl start. Now you can try login using your oracle user e.g : sqlplus scott@vlnxora1

If you get this problem in your oracle client installation, please make sure that your service name is configured correctly. To check that, do the following steps :

1. Edit tnsnames.ora
2. Make sure that service name in connect descriptor is same with oracle database service name.
3. To check oracle database service name, login to your oracle database server and execute the query below : 

   sqlplus / as sysdba
   select value from v$parameter where name='service_names';
   show parameter service_names;

4. To change oracle service name you can use alter

   ALTER SYSTEM SET SERVICE_NAMES='application_a.your.domain','application_b.your.domain' SCOPE=BOTH;