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
3 thoughts on “Change in Database link – 11gR2”
Good Deal, really helped!!!
Small and useful tidbits…
Nice to Know that. Thank you