View source for Create and use database link

<syntaxhighlight lang=sql>
-- connect as system admin and grant user1
connect system/password1@TNS_NAME1
grant create database link to user1;

-- connect as user1
connect user1/password2@TNS_NAME1;

-- create link using tns name (as configured in tnsnames.ora)
DROP DATABASE LINK dblink1;
CREATE DATABASE LINK dblink1
    CONNECT TO remote_schema1 IDENTIFIED BY remote_password3
    USING 'TNS_NAME2';

-- create link using complete TNS entry
DROP DATABASE LINK dblink1;
CREATE DATABASE LINK dblink1
    CONNECT TO remote_schema1 IDENTIFIED BY remote_password3
    USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = servername.local)(PORT = 1521)))(CONNECT_DATA = (SID = orcl)))';

-- test
SELECT * FROM dual@dblink1;

</syntaxhighlight>

[[Category:SynUpd]]
[[Category:Oracle]]
[[Category:Oracle Admin]]
[[Category:2009]]