Db link is a useful mechanism to pull data from external database.
Database link use TCP/IP connection during connection establishment with external database & fetching data.
To create Database like --
You must have CREATE DATABASE LIKE privilege
The Account using which your system will be connecting the remote database must have CREATE SESSION system privilege.
Both the above privileges are included as part of CONNECT role in oracle.
Database link can be created using following command –
CREATE [PUBLIC|PRIVATE|SHARED] DATABASE LINK NAME_OF_DB_LINK
CONNECT TO USER_ID IDENTIFIED BY PASSWORD
USING 'CONNECTION_STRING';
|
PUBLIC: - PUBLIC Database link are available for all user in the database.
PRIVATE: - PRIVATE link are available for only to the user who creating it.
SHARED: - Shared link are useful to establish many concurrent database connection.
There are 2 ways to specify the connect string --
Specify the TNS Description in place of ‘CONNECTION_STRING'. In this case the sql command would be --
CREATE [PUBLIC|PRIVATE|SHARED] DATABASE LINK NAME_OF_DB_LINK
CONNECT TO USER_ID IDENTIFIED BY PASSWORD
USING
‘(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ABC7821.xyz.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = HWCRMSIT.xyz.com)
)
)’;
|
Create a Service Naming using Oracle Net Manager & specify the same service naming in ‘CONNECTION_STRING'
CREATE [PUBLIC|PRIVATE|SHARED] DATABASE LINK NAME_OF_DB_LINK
CONNECT TO USER_ID IDENTIFIED BY PASSWORD
USING ‘hwcrmqa’;
|
| 
No comments:
Post a Comment