A little change in dblink from 11gR2 –

SQL> conn anand/anand123
Connected.
SQL>
SQL> @db_link
OWNER                          DB_LINK                        USERNAME                       HOST
------------------------------ ------------------------------ ------------------------------ ------------------------------
ANAND                          SCOTT_DBLINK                   SCOTT                          MATSTDBY
SQL> select sysdate from [email protected]_DBLINK;
SYSDATE
---------
11-MAY-12

I login as sys and change scott schema’s password –

SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL>
SQL> alter user scott identified by abc;
User altered.

Now select using the dblink fails –

SQL> select sysdate from [email protected]_DBLINK;
select sysdate from [email protected]_DBLINK
                         *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from SCOTT_DBLINK

In prior to 11gR2 release, one had to drop and recreate the dblink with the correct password. From 11gR2 we have “alter database link” command –

SQL> alter database link SCOTT_DBLINK connect to scott identified by abc;
alter database link SCOTT_DBLINK connect to scott identified by abc
                    *
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> grant alter database link to anand;
Grant succeeded.
SQL> conn anand/anand123
Connected.
SQL>
SQL>
SQL>
SQL> alter database link SCOTT_DBLINK connect to scott identified by abc;
Database link altered.
SQL> select * from [email protected]_DBLINK;
D
-
X