生产库上要修改生产用户的密码，密码修改完后就涉及到修改dblink的密码。
11gR2版本之前只能删除dblink后重建，11gR2版本后可以直接修改。
修改dblink的密码有两种方法：
1、删除dblink后重建
2、直接修改dblink的用户密码
下面测试两种方法：
测试库版本：11.2.0.4.0
创建两个dblink，一个PUBLIC另一个PRIVATE
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
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
---
X
sys@ORCL>
select
*
from
dual@link_102;
DUM
---
X
|
修改远端数据库密码
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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 denied
ORA-02063: preceding line
from
LINK_101
sys@ORCL>
select
*
from
dual@link_102;
select
*
from
dual@link_102
*
ERROR
at
line 1:
ORA-01017: invalid username/
password
; logon denied
ORA-02063: preceding line
from
LINK_102
|
方法一：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
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
---
X
sys@ORCL>
select
*
from
dual@link_102;
DUM
---
X
|
方法二：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
sys@ORCL>
select
*
from
dual@link_101;
select
*
from
dual@link_101
*
ERROR
at
line 1:
ORA-01017: invalid username/
password
; logon denied
ORA-02063: preceding line
from
LINK_101
sys@ORCL>
select
*
from
dual@link_102;
select
*
from
dual@link_102
*
ERROR
at
line 1:
ORA-01017: invalid username/
password
; logon denied
ORA-02063: preceding line
from
LINK_102
sys@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
---
X
sys@ORCL>
select
*
from
dual@link_102;
DUM
---
X
|
方法二如果提示ORA-01031: insufficient privileges，则赋权grant alter database link to zx;
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1875366，如需转载请自行联系原作者
