Oracle modifies Database Link password
The production library needs to change the password of the production user. After the password change, it involves changing the password of dblink.
Before the 11gR2 version, you can only delete the dblink and rebuild it, and you can modify it directly after the 11gR2 version.
There are two ways to change the password of dblink:
1. Rebuild after deleting dblink
2. Directly modify the user password of dblink
Here are two ways to test:
Test library version: 11.2.0.4.0
Create two dblink, one PUBLIC and one PRIVATE
Create PUBLIC database link link_101 connect to SYSTEM identified by "123456" using'(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.56.11) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydb)' Create database link link_102 connect to SYSTEM identified by "123456" using'(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.56.11) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydb)'; sys@ORCL > select * from dual@link_101;DUM---Xsys@ORCL > select * from dual@link_102;DUM---X
Modify remote database password
SQL > alter user system identified by "654321"; User altered.sys@ORCL > select * from dual@link_101;select * from dual@link_101 * ERROR at line 1:ORA-01017: invalid username/password; logon deniedORA-02063: preceding line from LINK_101sys@ORCL > select * from dual@link_102;select * from dual@link_102 * ERROR at line 1:ORA-01017: invalid username/password; logon deniedORA-02063: preceding line from LINK_102
Method 1:
Sys@ORCL > drop public database link link_101;Database link dropped.sys@ORCL > drop database link link_102;Database link dropped.create PUBLIC database link link_101 connect to SYSTEM identified by "654321" using'(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.56.11) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydb)' Create database link link_102 connect to SYSTEM identified by "654321" using'(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.56.11) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydb)'; sys@ORCL > select * from dual@link_101;DUM---Xsys@ORCL > select * from dual@link_102;DUM---X
Method 2:
Sys@ORCL > select * from dual@link_101;select * from dual@link_101 * ERROR at line 1:ORA-01017: invalid username/password; logon deniedORA-02063: preceding line from LINK_101sys@ORCL > select * from dual@link_102;select * from dual@link_102 * ERROR at line 1:ORA-01017: invalid username/password; logon deniedORA-02063: preceding line from LINK_102sys@ORCL > alter public database link link_101 connect to system identified by "654321" Database link altered.sys@ORCL > alter database link link_102 connect to system identified by "654321"; Database link altered.sys@ORCL > select * from dual@link_101;DUM---Xsys@ORCL > select * from dual@link_102;DUM---X
Method 2: if prompted by ORA-01031: insufficient privileges, grant alter database link to zx is given weight.
