Create Database Link

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.
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

Potrebbero interessarti anche...