最近生产数据库要修改生产用户的密码，由于用户密码修改，各个库之间创建的dblink就失效了，需要重建。上生产一看dblink还真不少，而且谁建的都有，改起来比较费劲。于是想到可以使用expdp的方式导出所有的dblink，即可获取dblink的创建语句，然后修改原密码再在库上创建就可以了。
生产环境中的库有两个版本10.2.0.4和11.2.0.3下面分别在10g和11g上做测试
测试10g：
|
1
2
3
4
5
6
7
8
9
|
SQL>
select
*
from
v$version;
BANNER
----------------------------------------------------------------
Oracle
Database
10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS
for
Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
|
创建dblink到11g数据库
|
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
|
SQL>
create
public
database
link link_11g
connect
to
system identified
by
"123456"
using
'(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)'
;
Database
link created.
SQL>
select
*
from
dual@link_11g;
D
-
X
col owner
for
a30
col username
for
a30
col db_link
for
a30
col host
for
a50
set
linesize 200
set
pagesize 999
select
OWNER,DB_LINK,USERNAME,HOST
from
dba_db_links;
OWNER DB_LINK USERNAME HOST
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------
PUBLIC
LINK_11G SYSTEM (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.
2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
|
使用expdp导出dblink
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[oracle@rhel5 ~]$ expdp system/123456 directory=dump dumpfile=dblink.dmp full=y include=db_link
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 10 November, 2016 15:26:02
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=dump dumpfile=dblink.dmp full=y include=db_link
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/dblink.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 15:26:13
|
使用impdp查看导出的dblink的创建语句
|
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
|
[oracle@rhel5 ~]$ impdp system/123456 directory=dump dumpfile=dblink.dmp sqlfile=dblink.sql
Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 10 November, 2016 15:28:31
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_FULL_01": system/******** directory=dump dumpfile=dblink.dmp sqlfile=dblink.sql
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 15:28:33
[oracle@rhel5 ~]$ cat /home/oracle/dblink.sql
-- CONNECT SYSTEM
-- new object type path is: DATABASE_EXPORT/SCHEMA/DB_LINK
CREATE PUBLIC DATABASE LINK "LINK_11G"
CONNECT TO "SYSTEM" IDENTIFIED BY VALUES '0588B5151FD4089DC7B87F64727E740D26'
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)';
|
测试11g
|
1
2
3
4
5
6
7
8
9
|
sys@ORCL>
select
*
from
v$version;
BANNER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle
Database
11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS
for
Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
|
创建dblink
|
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
|
create
public
database
link link_10g
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)
)
)'
;
Database
link created.
sys@ORCL>
select
*
from
dual@link_10g;
DUM
---
X
sys@ORCL>
select
OWNER,DB_LINK,USERNAME,HOST
from
dba_db_links;
OWNER DB_LINK USERNAME HOST
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------
PUBLIC
LINK_A SCOTT orcl
PUBLIC
LINK_10G SYSTEM (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.
11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb)
)
)
|
expdp导出dblink
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
[oracle@rhel6 ~]$ expdp system/123456 directory=dump dumpfile=dblink.dmp full=y include=db_link
Export: Release 11.2.0.4.0 - Production on Thu Nov 10 15:34:24 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=dump dumpfile=dblink.dmp full=y include=db_link
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/dblink.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Thu Nov 10 15:34:42 2016 elapsed 0 00:00:15
|
使用impdp查看导出的dblink的创建语句
|
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
|
[oracle@rhel6 ~]$ impdp system/123456 directory=dump dumpfile=dblink.dmp sqlfile=dblink.sql
Import: Release 11.2.0.4.0 - Production on Thu Nov 10 15:35:38 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_FULL_01": system/******** directory=dump dumpfile=dblink.dmp sqlfile=dblink.sql
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at Thu Nov 10 15:35:40 2016 elapsed 0 00:00:01
[oracle@rhel6 ~]$ cat /home/oracle/dblink.sql
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/SCHEMA/DB_LINK
CREATE PUBLIC DATABASE LINK "LINK_10G"
CONNECT TO "SYSTEM" IDENTIFIED BY VALUES ':1'
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb)
)
)';
CREATE PUBLIC DATABASE LINK "LINK_A"
CONNECT TO "SCOTT" IDENTIFIED BY VALUES ':1'
USING 'orcl';
|
使用上面的方式可以查看dblink的创建语句。
从上面dblink创建语句的输出结果也可以看出10g导出的dblink创建语句可以看到密码的密文，而11g里就看不到了。
官方文档：http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm#i2061505
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1871495，如需转载请自行联系原作者
