Thursday, 28 March 2013

What was the password used to create db link?

You have a database link in one of your Oracle 11g database accounts and you want to create the exact db link in a different account. The only issue is that the user password is blank in user_db_links, due to security reasons. So how to find out the password and to "duplicate" this db link?
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





1 comment:

  1. 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:

    SQL> 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'

    ReplyDelete