11gR2 New Feature – Alter Database Link to Change Password

Our organization requires a regular password change on some database accounts for security compliance. If this account is used in the database link in other database, that database link has to be dropped and recreated with an updated password.

This changes in 11gR2 because it now offers the alter database link to change password. No more drop and recreate database link!

Sample here is on the database where database link is located:

The password of the database link’s account has just been changed.

db11gr2 SQL> select count(*) from tb_test@DL_TEST;
select count(*) from tb_test@DL_TEST
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from DL_TEST

db11gr2 SQL> alter database link DL_TEST connect to dblink_test identified by dblink_test;

Database link altered.

db11gr2 SQL> select count(1) from tb_test@DL_TEST;

COUNT(1)
----------
6304

This option is not available in the pre-11gR2.

db11gr1 SQL > alter database link DL_TEST connect to dblink_test identified by dblink_test;

alter database link DL_TEST connect to dblink_test identified by dblink_test
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

Related Topics:

• The Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g