生产库上要修改生产用户的密码,密码修改完后就涉及到修改dblink的密码。
11gR2版本之前只能删除dblink后重建,11gR2版本后可以直接修改。
修改dblink的密码有两种方法:
1、删除dblink后重建
2、直接修改dblink的用户密码
下面测试两种方法:
测试库版本:11.2.0.4.0
创建两个dblink,一个PUBLIC另一个PRIVATE
createPUBLICdatabaselinklink_101connecttoSYSTEMidentifiedby"123456"using'(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=mydb)
)
)';
createdatabaselinklink_102connecttoSYSTEMidentifiedby"123456"using'(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=mydb)
)
)';
sys@ORCL>select*fromdual@link_101;
DUM
---
X
sys@ORCL>select*fromdual@link_102;
DUM
---
X
修改远端数据库密码
SQL>alterusersystemidentifiedby"654321";
Useraltered.
sys@ORCL>select*fromdual@link_101;
select*fromdual@link_101
*
ERRORatline1:
ORA-01017:invalidusername/password;logondenied
ORA-02063:precedinglinefromLINK_101
sys@ORCL>select*fromdual@link_102;
select*fromdual@link_102
*
ERRORatline1:
ORA-01017:invalidusername/password;logondenied
ORA-02063:precedinglinefromLINK_102
方法一:
sys@ORCL>droppublicdatabaselinklink_101;
Databaselinkdropped.
sys@ORCL>dropdatabaselinklink_102;
Databaselinkdropped.
createPUBLICdatabaselinklink_101connecttoSYSTEMidentifiedby"654321"using'(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=mydb)
)
)';
createdatabaselinklink_102connecttoSYSTEMidentifiedby"654321"using'(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=mydb)
)
)';
sys@ORCL>select*fromdual@link_101;
DUM
---
X
sys@ORCL>select*fromdual@link_102;
DUM
---
X
方法二:
sys@ORCL>select*fromdual@link_101;
select*fromdual@link_101
*
ERRORatline1:
ORA-01017:invalidusername/password;logondenied
ORA-02063:precedinglinefromLINK_101
sys@ORCL>select*fromdual@link_102;
select*fromdual@link_102
*
ERRORatline1:
ORA-01017:invalidusername/password;logondenied
ORA-02063:precedinglinefromLINK_102
sys@ORCL>alterpublicdatabaselinklink_101connecttosystemidentifiedby"654321";
Databaselinkaltered.
sys@ORCL>alterdatabaselinklink_102connecttosystemidentifiedby"654321";
Databaselinkaltered.
sys@ORCL>select*fromdual@link_101;
DUM
---
X
sys@ORCL>select*fromdual@link_102;
DUM
---
X
方法二如果提示ORA-01031: insufficient privileges,则赋权grant alter database link to zx;