Dblink
When we want to access data from another database table across a local database, the local database must create a dblink of the remote database, and the local database can access the data in the Remote database table as if it were accessing the local database.
How to create a Dblink
1) SQL Server to SQL Server
Exec sp_droplinkedsrvlogin Pdalink,null--Deleting mappings (mapping to remote logins on linked servers)
Exec sp_dropserver Pdalink--Remove remote server link
EXEC sp_addlinkedserver
@server = ' Pdalink ',--The server alias being accessed
@srvproduct = ' ',--The product name of the OLE DB data source to be added as a linked server
@provider = ' SQLOLEDB ',--Accessed Database category Msdaora SQLOLEDB
@datasrc = ' 192.168.120.114 '--the server being accessed
EXEC sp_addlinkedsrvlogin
' Pdalink ', --The server alias being accessed
' False ', --
Null --Login on the local server. The LocalLogin data type is sysname and is set to NULL by default.
' Sa ', --Account Number
' Sa ' --Password
SELECT * from Pdalink. [Database].dbo. [table name, view]
2) Oracle to Oracle
Drop/* Public */Database link Pda_link--Remove the remote server link
Create/* Public */Database link pda_link--the server alias being accessed
Connect to SYSTEM identified by Frontlink
Using ' (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.1.10)--the server being accessed
(PORT = 1521)))--the port being accessed
(Connect_data =
(service_name = SMB)--the database being accessed
))‘;
SELECT * FROM [table name, view] @PDA_LINK
Create DBLINK in SQL Server and Oracle
