Create Database Link
After you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.
Prerequisites
To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.
Oracle Net must be installed on both the local and remote Oracle databases.
The following link types are supported:
• Private database link – belongs to a specific schema of a database. Only the owner of a private database link can use it.
• Public database link – all users in the database can use it.
Specify PUBLIC to create a public database link available to all users. If you omit this clause, the database link is private and is available only to you.
• Global database link – defined in an OID or Oracle Names Server. Anyone on the network can use it.
The steps to create a database link are:
• Choose the tables that will make up the view from Database DB1
• Defined alias in TNSNAMES.ora file
• Create public Database link into Database DB2
• Verify that Database link is created
• Create view with Database link
• Verify that the view appears in the database DB2
CHOOSE THE TABLES THAT WILL MAKE UP THE VIEW FROM DATABASE DB1
Database >select*from meteo;
NOME CONDIZIONE TEMP
——————– ——————– ——————–
milano sereno 20
siena nuvoloso 15
napoli sereno 28
NOME CONDIZIONE TEMP
——————– ——————– ——————–
roma pioggia 18
genova sereno 19
Database >select*from clima;
NOME TEMP_MAX REGIONE
——————– ——————– ——————–
milano 25 lombardia
siena 19 toscana
napoli 33 campania
roma 22 lazio
genova 24 liguria
DEFINED ALIAS IN TNSNAMES.ORA FILE
LSNR_DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10…)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DB1)
)
)
DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10…)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB1)
)
CREATE PUBLIC DATABASE LINK INTO DATABASE DB2
Database > CREATE PUBLIC DATABASE LINK RC
2 CONNECT TO user
3 IDENTIFIED BY password
4 USING ‘DB1′
5 /
VERIFY THAT DATABASE LINK IS CREATED
Database > select*from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
—————– ————- ————– ———— ———
PUBLIC RC user DB1 04-GIU-13
CREATE VIEW WITH DATABASE LINK
Database > CREATE VIEW TEMPO AS SELECT METEO.NOME,METEO.CONDIZIONE,METEO.TEMP,CLIMA.TEMP_MAX,CLIMA.REGIONE FROM user.METEO@RC, user.CLIMA@RC WHERE METEO.NOME=CLIMA.NOME;
VERIFY THAT THE VIEW APPEARS IN THE DATABASE DB2
Database > select*from TEMPO;
NOME CONDIZIONE TEMP TEMP_MAX REGIONE
———— ——————– ———– ————- ———-
milano sereno 20 25 lombardia
siena nuvoloso 15 19 toscana
napoli sereno 28 33 campania
roma pioggia 18 22 lazio
genova sereno 19 24 liguria
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm
http://www.orafaq.com/wiki/Database_link