Oracle 修改Database Link密码

生产库上要修改生产用户的密码，密码修改完后就涉及到修改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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!