用dblink能修改_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;