EXPDP Export Only Dblink

Recent production database to modify the production user's password, due to user password modification, the creation of dblink between the libraries is invalid, need to rebuild. On the production of a look at Dblink is really a lot, and who built all have, change up more laborious. So think of the way you can use EXPDP to export all the Dblink, you can get Dblink creation statement, and then modify the original password and then create on the library.

The libraries in the production environment have two versions of 10.2.0.4 and 11.2.0.3 under test on 10g and 11g respectively.

Test 10g:

Sql> select * from V$version; BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition  Release 10.2.0.4.0-64bipl/sql release 10.2.0.4.0-productioncore10.2.0.4.0productiontns for Linux:version 10.2.0.4.0- Productionnlsrtl Version 10.2.0.4.0-production

Create a dblink to a 11g database

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 [email protected]_11g;d-xcol owner for a30col  username for a30col db_link for a30col host for a50set  Linesize 200set pagesize 999select owner,db_link,username,host from dba_db_links ;owner       db_link      username    &nBsp; host------------------------------ ------------------------------ ------------------------ ------ --------------------------------------------------public        link_11g       system      (DESCRIPTION =   (address =  (protocol = tcp) (host = 192.168.56.      2) (port = 1521)   (connect_data =    (server = dedicated)     (SERVICE_NAME = ORCL)  )        )

Export Dblink using EXPDP

[[Email protected] ~]$ expdp system/123456 directory=dump dumpfile=dblink.dmp  full=y include=db_linkExport: Release 10.2.0.4.0 - 64bit Production  on thursday, 10 november, 2016 15:26:02copyright  (c)  2003, 2007,  Oracle.  all rights reserved. connected to: oracle database 10g enterprise edition release  10.2.0.4.0 - 64bit productionwith the partitioning, olap, data mining  and Real Application Testing optionsFLASHBACK 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 D atabase_export/schema/table/table_datatotal estimation using blocks method: 0  kbprocessing object type database_export/schema/db_linkmaster table  "SYSTEM". " Sys_export_full_01 " successfully loaded/unloaded**************************************************** Dump file set for system. sys_export_full_01 is:  /home/oracle/dblink.dmpjob  "SYSTEM". " Sys_export_full_01 " successfully completed at 15:26:13

Use IMPDP to view the created statements for the exported Dblink

[[Email protected] ~]$ impdp system/123456 directory=dump dumpfile=dblink.dmp  sqlfile=dblink.sqlImport: Release 10.2.0.4.0 - 64bit Production on  thursday, 10 november, 2016 15:28:31copyright  (c)  2003, 2007, oracle.   all rights reserved. connected to: oracle database 10g enterprise edition release  10.2.0.4.0 - 64bit productionwith the partitioning, olap, data mining  and Real Application Testing optionsMaster table  "SYSTEM". " Sys_sql_file_full_01 " successfully loaded/unloadedStarting " SYSTEM "." Sys_sql_file_full_01 ":   system/******** directory=dump dumpfile=dblink.dmp sqlfile= dblink.sql processing object type database_export/schema/db_linkjob  "SYSTEM". " Sys_sql_file_full_01 " successfully completed at 15:28:33[[email protected] ~]$ cat /home/oracle/ Dblink.sql-- connect system-- new object type path is: database_export /schema/db_linkcreate 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) (    )   ) ';

Test 11g

[Email Protected]>select * from v$version; BANNER------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition release 11.2.0.4.0-64bit productionpl/sql release 11.2.0.4.0- Productioncore11.2.0.4.0productiontns for linux:version 11.2.0.4.0-productionnlsrtl Version 11.2.0.4.0-production

Create Dblink

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)        ) ';D atabase link created. [Email protected]>select * from [email protected]_10g;dum---X[email protected ]>select owner,db_link,username,host from dba_db_links;owner        DB_LINK      USERNAME      HOST------------------------------ ------------------------------ ------------------------------  --------------------------------------------------Public       link_a      scott      orclPUBLIC       LINK_10G        SYSTEM      (description =  (address =  (protocol =  TCP) (host = 192.168.56.     11) (port = 1521))   (CONNECT_DATA  =    (server = dedicated)     (service_name = mydb)   )        )

EXPDP Export Dblink

[[Email protected] ~]$ expdp system/123456 directory=dump dumpfile=dblink.dmp  full=y include=db_linkexport: release 11.2.0.4.0 - production on thu  Nov 10 15:34:24 2016Copyright  (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 productionwith the partitioning, olap, data mining  and Real Application Testing optionsFLASHBACK 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 kbprocessing object type database_export/schema/ db_linkmaster table  "SYSTEM". " Sys_export_full_01 " successfully loaded/unloaded**************************************************** Dump file set for system. sys_export_full_01 is:  /home/oracle/dblink.dmpjob  "SYSTEM". " Sys_export_full_01 " successfully completed at thu nov 10 15:34:42 2016  elapsed 0 00:00:15

Use IMPDP to view the created statements for the exported Dblink

[[Email protected] ~]$ impdp system/123456 directory=dump dumpfile=dblink.dmp  sqlfile=dblink.sqlimport: release 11.2.0.4.0 - production on thu nov  10 15:35:38 2016Copyright  (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 productionwith the partitioning, olap, data mining  and Real Application Testing optionsMaster table  "SYSTEM". " Sys_sql_file_full_01 " successfully loaded/unloadedStarting " SYSTEM "." Sys_sql_file_full_01 ":   system/******** directory=dump dumpfile=dblink.dmp sqlfile= dblink.sql processing object type database_export/schema/db_linkjob  "SYSTEM". " Sys_sql_file_full_01" successfully completed at thu nov 10 15:35:40 2016 elapsed  0 00:00:01[[email protected] ~]$ cat /home/oracle/dblink.sql-- connect  SYSTEMALTER 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_linkcreate 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 ';

Use the above method to view the Dblink creation statement.

The output from the Dblink Create statement above can also be seen in the 10g exported Dblink creation statement can see the cipher ciphertext, and 11g will not see.

Official Document: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm#i2061505

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1871495

EXPDP Export Only Dblink