DB-link creation or Db-link copy from 9i to 10g and 10g to 11g.

In 9i version sys.link$ password column stored with password. During migration activity from 9i to 10g/11g DBA can recreate db-link using below script.
Target Database db-link owner password change to manager99

Take backup of in target database and source database password backup.

set linesize 200
set pagesize 30000
spool password_backup.sql
select 'ALTER USER 'USERNAME' IDENTIFIED BY VALUES '''PASSWORD''' PROFILE 'PROFILE';' from dba_users ORDER BY USERNAME;
spool off


Below script will help only when database is migrated from 9i to 10g or 11g and db-links are not copied or missing.

In Source Database.


Extract db-link script by executing in source and o/p of below script execute in target.

Set linesize 200
Set pagesize 30000
Spool db_link_creation.sql
SELECT 'connect 'u.name'/''manager99' ,
'create 'DECODE(U.NAME,'PUBLIC','public ')'database link 'CHR(10)
L.NAMEchr(10)
'connect to ' L.USERID ' identified by '
L.PASSWORD' using ''' L.host ''''
chr(10)';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER#
order by l.OWNER#
/
Spool off

Example

connect Testdba/manger99
create database link test123.WORLD connect to test identified by values 'test234' using 'TEMP05';

Db-link copy from 10g to 11g database.
Since oracle password column is null. In this senario DBA still copy db_links using encrypted password. In 10g/11g sys.link$ is stored with encrypted password at location PASSWORDX.

Take backup of in target database and source database password backup.

set linesize 200
set pagesize 30000
spool password_backup.sql
select 'ALTER USER 'USERNAME' IDENTIFIED BY VALUES '''PASSWORD''' PROFILE 'PROFILE';' from dba_users ORDER BY USERNAME;
spool off


chage passwords of all schemas at target database to manager99.

set linesize 200
set pagesize 30000
spool password_change.sql
select 'ALTER USER 'USERNAME' IDENTIFIED BY VALUES manager99'';' from dba_users ORDER BY USERNAME;
spool off


SQL> desc sys.link$
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)
CTIME NOT NULL DATE
HOST VARCHAR2(2000)
USERID VARCHAR2(30)
PASSWORD VARCHAR2(30)
FLAG NUMBER
AUTHUSR VARCHAR2(30)
AUTHPWD VARCHAR2(30)
PASSWORDX RAW(128)
AUTHPWDX RAW(128)



Set linesize 200
Set pagesize 30000
Spool db_link_creation.sql
SELECT 'connect 'u.name'/''manager99' ,
'create 'DECODE(U.NAME,'PUBLIC','public ')'database link 'CHR(10)
L.NAMEchr(10)
'connect to ' L.USERID ' identified by values '''
L.PASSWORDX''' using ''' L.host ''''
chr(10)';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER#
order by l.OWNER#
/
spool off



Example

connect testdba/manager99
create database link test123.world connect to test1 identified by values '05A2E8411F97EC0539E47A76AE2EC191609DDA29EBBA5CDC0E' using 'TEST' ;