Oracle : ORA-01017, ORA-02063 preceding line from DATABASE_LINK

while creating a database link, I accidentally provided the wrong password of the remote user and while accessing the database link, it got the below error

 

SQL> select * from TECH_AS_OWNER.AS_GARD@asgard_link;

select * from TECH_AS_OWNER.AS_GARD@asgard_link

                                    *

ERROR at line 1:

ORA-01017: invalid username/password; logon denied

ORA-02063: preceding line from ASGARD_LINK

 

Error is quite clear as the password is invalid for a remote account, you have a couple of options to correct it, 

one way is to drop the existing database link and create it again and the other way is to alter the database link, 

 

we will discuss the second option, here i.e., altering the database link.  

to alter the database link, you must have certain privileges as mentioned in the below table 

 

Type of database link	Privilege required
Private	ALTER DATABASE LINK
Public	ALTER PUBLIC DATABASE LINK

 

Below are the commands to alter the database link 

--grant privilege alter database link

SQL> grant alter database link to tech_owner;

 

SQL> select grantee, privilege from dba_sys_privs where grantee='TECH_OWNER'; 

GRANTEE                        PRIVILEGE

------------------------------ ----------------------------------------

TECH_OWNER                     ALTER DATABASE LINK

TECH_OWNER                     CREATE DATABASE LINK

 

SQL> ALTER DATABASE LINK asgard_link

  2  CONNECT TO TECH_AS_OWNER IDENTIFIED BY User#123; 

Database link altered.

 

--verify if database link is working

 

SQL> select sysdate from dual@asgard_link; 

SYSDATE

---------

29-AUG-22 

 

Similarly we can perform activity for public database link