I. Database link from Oracle to Oracle
I used OracleXE (Oracle eXprEss) database.
I created a database link from one schema to another schema(all within oracle XE database)
1. A and B are two USERS (schema’s)
2. Employee table is in B
3. In OracleXE, the service name is XE(usually Orcl)
4. Database link from A to B is created as follows :
e.g…
CREATE PUBLIC DATABASE LINK XE@XE
CONNECT TO B
IDENTIFIED BY B’SPASSWORD
USING 'XE'
/
Here link name is XE@XE
First XE is used because there is some constraint regarding global_names so it is same as service name.
Second XE is due to that loopback link should append @XE.
5. select * from Employee@xe@xe
Eno Ename
----------------------
1 AAA
2 BBB
II. Database link from Oracle to MS SQL
Database link from Oracle to other database can be done by
a. Transparent Gateways
b. Connectivity
i. OLEDB
ii. ODBC
I used OBDC to create the link.
Steps to create oracle to MS SQL 2000 is available in
1) http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1
Finally the tnsnames.ora should have something like these(after having a oracle to oracle link and oracle to ms sql server link)
MYDB2DSN= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1523)) (CONNECT_DATA=(SID=MYDB2DSN)) (HS=OK) )
MYSQLSERVERDSN = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) (CONNECT_DATA=(SID=MYSQLSERVERDSN)) (HS=OK) )
XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )
The same holds good for db2 also
References:
1) http://it.toolbox.com/blogs/oracle-guide/accessing-multiple-xe-databases-from-a-single-machine-6839
2) http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1
3)http://www.databasejournal.com/features/oracle/article.php/10893_3442661_2
