Oracle provides database links to create direct connections from one database instance to another. This feature can come in handy in the movement of small to moderate amounts of data between systems. In this article we will review the creation of the DDL to regenerate public database links defined within an Oracle database.
1. Connect to your Oracle database server and logon to SQL*PLUS.
MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 3 19:21:09 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2. Create a public database link from your database to another database to demonstrate generation of your DDL.
create public database link oracle11
connect to ljcatt identified by ljcatt
using ‘(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g)
)
)’;
SQL> create public database link oracle11
2 connect to ljcatt identified by ljcatt
3 using ‘(DESCRIPTION =
4 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1522))
5 (CONNECT_DATA =
6 (SERVER = DEDICATED)
7 (SERVICE_NAME = orcl11g)
8 )
9 )’;
Database link created.
SQL>
3. Execute the following PL/SQL block to generate your DB Links DDL.
set serveroutput on
declare
v_pwd varchar2(30);
begin
for rec in(select * from dba_db_links where owner=’PUBLIC’)
loop
dbms_output.put_line(‘create public database link ‘||rec.db_link);
dbms_output.put_line(‘connect to ‘||rec.username||’ identified by ‘||'(password)’);
dbms_output.put_line(‘using ‘||””||rec.host||””||’;’);
end loop;
end;
/
SQL> set serveroutput on
SQL> declare
2
3 v_pwd varchar2(30);
4
5 begin
6
7 for rec in(select * from dba_db_links where owner=’PUBLIC’)
8 loop
9
10
11 dbms_output.put_line(‘create public database link ‘||rec.db_link);
12 dbms_output.put_line(‘connect to ‘||rec.username||’ identified by ‘||'(pass
word)’);
13 dbms_output.put_line(‘using ‘||””||rec.host||””||’;’);
14
15
16 end loop;
17 end;
18 /
create public database link ORACLE11.REGRESS.RDBMS.DEV.US.ORACLE.COM
connect to LJCATT identified by (password)
using ‘(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT =
1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g)
)
)’;
PL/SQL procedure successfully completed.
SQL>
4. Now you can use the DDL generated to move or recreate your public database link. NOTE: In Oracle 9i you can retrieve the password through the view sys.link$. However, in Oracle 10g and on, all passwords are encrypted, thus you must manually insert the actual password.
Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com