Direct Method to Create Database Link Without Using TNSnames.ora

_____________________________________________________________________________________________________________________

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


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

_____________________________________________________________________________________________________________________

0 comments:

Post a comment

 

acehints.com Copyright 2011-20 All Rights Reserved | Site Map | Contact | Disclaimer