Alter Database link

We can use ALTER DATABASE LINK to modify database link whenever we need to change password. In case of changing username and host we need to re-create database link.


Create and verify public database link with command below.
CREATE PUBLIC DATABASE LINK dblink_test
CONNECT TO dilli IDENTIFIED BY oracle
USING 'TESTDB';

SELECT property_value
FROM database_properties@dblink_test
WHERE property_name='GLOBAL_DB_NAME';

In case of password change for the user dilli we will encounter the error below.

FROM database_properties@dblink_test

                         *

ERROR at line 2:

ORA-01017: invalid username/password; logon denied

ORA-02063: preceding line from DBLINK_TEST

We can use alter command to change the password used on dblink. Execute following command as user sys.

ALTER PUBLIC DATABASE LINK dblink_test
CONNECT TO dilli IDENTIFIED BY oracle_1;

If you need to modify username or hostname then you need to re-create the dblink. Before re-creation generate DDL of the database link using DBMS_METADATA package. We will use get_ddl function to generate DDL of database link. Return value will be of datatype CLOB so set long to 1000 characters before execution of the command otherwise incomplete DDL will be generated. Execute following command as user sys.

set long 1000
SELECT dbms_metadata.get_ddl('DB_LINK','DBLINK_TEST','PUBLIC')
FROM dual;

Now we can drop the database link and create with the SQL noted right now. Make sure there is no new line in value and the value is intact in single line as below.

To modify username use command below. In our case we have switched the username RAJIV with exactly same password as user DILLI has.

DROP PUBLIC DATABASE LINK dblink_test;

CREATE PUBLIC DATABASE LINK "DBLINK_TEST"
CONNECT TO "RAJIV" IDENTIFIED BY VALUES '0677433333EA295E225C0B92B30170664D756DA1C83756F6720A0E40DF81A0B0329BC3CF628AEE972A9E262984B1E1F26E09505A801B51EBF7372A8E79990112935DC1DECB60940B2469089A2237438C87FF97092153396834506130DA8AA8ECEEF4B61A9320688675D83B8C93860821FCFB678FA6C49827C40215D683CD6E0B'
USING 'TESTDB';

To modify host, execute command below as user sys.

DROP PUBLIC DATABASE LINK dblink_test;
CREATE PUBLIC DATABASE LINK "DBLINK_TEST"
CONNECT TO "DILLI" IDENTIFIED BY VALUES '0677433333EA295E225C0B92B30170664D756DA1C83756F6720A0E40DF81A0B0329BC3CF628AEE972A9E262984B1E1F26E09505A801B51EBF7372A8E79990112935DC1DECB60940B2469089A2237438C87FF97092153396834506130DA8AA8ECEEF4B61A9320688675D83B8C93860821FCFB678FA6C49827C40215D683CD6E0B'
USING 'PRODDB';