Direct Method to Create Database Link Without Using TNSnames.ora

advertisements

_____________________________________________________________________________________________________________________

Normal method – Using TNSnames service name 

This method requires the TNS entry to be added in the TNSnames.ora file as mentioned below. 

Sample TNSNames.ora entry

PRODAPP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-19.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prodapp)
    )
  )

SQL Syntax

SQL> 	create public database link prodapp 
	connect to app_user identified by app_user using 'PRODAPP';

Database link created.

SQL> select 1 from dual@prodapp; 

	 1
----------
	 1

advertisements

 

Without using TNSnames.ora 

Method 1. 

Using TNS description in the create command

SQL> 	create public database link prodapp 
	connect to app_user identified by app_user USING
'(DESCRIPTION=(ADDRESS_LIST=(
 ADDRESS=(PROTOCOL=TCP)(HOST=ol7-19.localdomain)(PORT=1521)))
 (CONNECT_DATA=(SERVICE_NAME=PRODAPP)))'  

Database link created.

SQL> select 1 from dual@prodapp; 

	 1
----------
	 1

Method 2. 

By embedding ‘Host:ListnerPORT/Database name’

SQL> create public database link PRODAPP 
connect to app_user identified by app_user USING
     'ol7-19.localdomain:1521/PRODAPP';
     
Database link created.


SQL> select 1 from dual@prodapp; 

	 1
----------
	 1

_____________________________________________________________________________________________________________________

Website Stats