In reality, you don't even need to know the password, you can query sys.link$ and use "identified by values" when creating the db link:
SQL> conn / as sysdba
Connected.
SQL> select NAME,HOST,userid,PASSWORDX
from sys.link$ where name='TEST1';
SQL> /
NAME HOST USERID PASSWORDX
-------------------- ---------- ------------------------------ --------------------------------------------------------------------------------
TEST1 MYDB FLORIN 05177093C45ABC2976294C0780B2B5873E3CE6D80F94D9A314
So all is left is to connect and create the db link:
SQL> conn florin2/florin2
Connected.
SQL> create database link test1 connect to florin identified by values '05177093C45ABC2976294C0780B2B5873E3CE6D80F94D9A314' using 'MYDB';
Database link created.
SQL> select * from dual@test1;
D
-
X
Just found out a more elegant way to restore the statement used to initially create a database link and this is using dbms_metadata.get_ddl function, see example below:
ReplyDeleteSQL> set line 200 pages 80 long 200000000 longchunksize 200
SQL> select dbms_metadata.get_ddl('DB_LINK','TEST1') from dual;
DBMS_METADATA.GET_DDL('DB_LINK','TEST1')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE DATABASE LINK "TEST1"
CONNECT TO "FLORIN" IDENTIFIED BY VALUES '0596C1CE2741CB1A183CF5CC9303C2B64740DB76D5145B51C7'
USING 'MYDB'