Google IT Solutions: How to Create /Drop /DB link in oracle

Wednesday 18 June 2014

How to Create /Drop /DB link in oracle

Two way to create DB link in oracle Database
1. With TNS entry in tnsnames.ora
2. TNS entry given at the time of db link creation.

Create DB link

1st method: Create DBLINK with TNS entry name made in tnsnames.ora
Put a TNS in tnsnames.ora file with name like “TEST”

CREATE PUBLIC DATABASE LINK 'TEST'
 CONNECT TO 'USER Name'
 IDENTIFIED BY 'Password'
 USING 'TEST'

TNS entry like;
TEST=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=hostname)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=test)
    )
  )

2nd method with full TNS

CREATE PUBLIC DATABASE LINK “TEST”
 CONNECT TO “USER Name”
 IDENTIFIED BY “Password”
 USING TEST=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=hostname)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=test)
    )
  )


DROP DBLINK:

DROP PUBLIC DATABASE LINK ‘TEST’


No comments:

Post a Comment