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