ORA-01017: invalid username/password; logon denied - Database Link

Error :
ORA-01017: invalid username/password; logon denied - Database Link

SQL> select * from global_name@my_link;
select * from global_name@my_link
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from MY_LINK

SQL>

Above error may sometimes occur when accessing the target DB with newly created database link.

If so,try connecting to the target database via SQLPlus using the tnsnames.ora entry: sqlplus pub@tnsname

If the target db is 11g then password case sensitivity may be the issue. 

Solution:

Place the password in double quotes when you create the database link and retry.

Drop and Create an User in Oracle

You can use below statements to drop an existing user and recreate it with appropriate privileges..




DROP USER MY_USER CASCADE
/


CREATE USER MY_USER
    IDENTIFIED BY MY_USER
    DEFAULT TABLESPACE MYTBS
    TEMPORARY TABLESPACE temp
/
GRANT DBA TO MY_USER
/
GRANT SELECT ANY TABLE TO MY_USER
/
GRANT INSERT ANY TABLE TO MY_USER
/
GRANT UPDATE ANY TABLE TO MY_USER
/
GRANT DELETE ANY TABLE TO MY_USER
/
GRANT ALTER ANY TABLE TO MY_USER
/
GRANT EXECUTE ANY PROCEDURE TO MY_USER
/
GRANT SELECT ANY SEQUENCE to MY_USER
/