Change in Database link – 11gR2

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 dual@SCOTT_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 dual@SCOTT_DBLINK;
select sysdate from dual@SCOTT_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 dual@SCOTT_DBLINK;

D
-
X
Advertisements

3 thoughts on “Change in Database link – 11gR2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s