get_db_link_scripts

来源：339美食

在用exp按用户进行导出的时候没有导出db_link语句。由于版本太老不能使用dbms_metaDATA.get_ddl获得。通过查询link$用sql拼接创建语句。 sql语句拼接 SQL @DESC link$; Name NULL? TYPE ------------------------------- -------- -------------------------

在用exp按用户进行导出的时候没有导出db_link语句。由于版本太老不能使用dbms_metaDATA.get_ddl获得。通过查询link$用sql拼接创建语句。

sql语句拼接

SQL> @DESC link$;
 Name NULL? TYPE
 ------------------------------- -------- ----------------------------
 1 OWNER# NOT NULL NUMBER
 2 NAME NOT NULL VARCHAR2(128)
 3 CTIME NOT NULL DATE
 4 HOST VARCHAR2(2000)
 5 USERID VARCHAR2(30)
 6 PASSWORD VARCHAR2(30)
 7 FLAG NUMBER
 8 AUTHUSR VARCHAR2(30)
 9 AUTHPWD VARCHAR2(30)
 10 PASSWORDX RAW(128)
 11 AUTHPWDX RAW(128)
 
SQL> @DESC USER$
 Name NULL? TYPE
 ------------------------------- -------- ----------------------------
 1 USER# NOT NULL NUMBER
 2 NAME NOT NULL VARCHAR2(30)
 3 TYPE# NOT NULL NUMBER
 4 PASSWORD VARCHAR2(30)
 5 DATATS# NOT NULL NUMBER
 6 TEMPTS# NOT NULL NUMBER
 7 CTIME NOT NULL DATE
 8 PTIME DATE
 9 EXPTIME DATE
 10 LTIME DATE
 11 RESOURCE$ NOT NULL NUMBER
 12 AUDIT$ VARCHAR2(38)
 13 DEFROLE NOT NULL NUMBER
 14 DEFGRP# NUMBER
 15 DEFGRP_SEQ# NUMBER
 16 ASTATUS NOT NULL NUMBER
 17 LCOUNT NOT NULL NUMBER
 18 DEFSCHCLASS VARCHAR2(30)
 19 EXT_USERNAME VARCHAR2(4000)
 20 SPARE1 NUMBER
 21 SPARE2 NUMBER
 22 SPARE3 NUMBER
 23 SPARE4 VARCHAR2(1000)
 24 SPARE5 VARCHAR2(1000)
 25 SPARE6 DATE
 
CREATE DATABASE link aa3
 CONNECT TO ogg1
 IDENTIFIED BY ogg1
 USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.65)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl )
)
)';
 
 
SELECT 'CREATE DATABASE LINK '|| L.NAME ||' CONNECT TO ' || USERID || ' IDENTIFIED BY VALUES '||chr(39) || l.PASSWORDX ||chr(39)||' using ' ||chr(39)|| HOST||chr(39) link
FROM link$ l,
 USER$ u
WHERE l.OWNER# = u.USER#
 5 AND u.name='TRAVEL';
 
LINK
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE DATABASE LINK TEST1 CONNECT TO TRAVEL IDENTIFIED BY VALUES '06307628FF3A73A9C177ED8D3E190256FCBED62E1EC3DD10847F3230BECF08C9298C6C1BC4ABF16E6A3843133008C4A1622CA2463929A98EE88F1829573454067B6F1861101DAA4049AAD974855562FC410CE5A0F3A2E91F6F945E88288AC0E66FA298D7570BF5A1CE70D522F6E53E07C12237112FC4BB723E4AA0CDA3A825DF' USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.65)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =orcl)
)
)'
 
CREATE DATABASE LINK AA3 CONNECT TO OGG1 IDENTIFIED BY VALUES '06BA16CF75CF21FA683544FBB18E8FDC01BE754AA4DFA90CFB605658172AF45FB7CD9B01BF8872DA0CAC950EFF38D65BFC3A4EEB082822C6DBC680C5503D3A371217ADA11AB5724A1C4E0E69080C659ED0DBB784F7267174E526F975DCCD92116A3E37807DB2711240392FAA5174505A8E6FA60445495A73C3B8F804' USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.65)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl )
)
)'
 
CREATE DATABASE LINK AA5 CONNECT TO OGG1 IDENTIFIED BY VALUES '06BA16CF75CF21FA683544FBB18E8FDC01BE754AA4DFA90CFB605658172AF45FB7CD9B01BF8872DA0CAC950EFF38D65BFC3A4EEB082822C6DBC680C5503D3A371217ADA11AB5724A1C4E0E69080C659ED0DBB784F7267174E526F975DCCD92116A3E37807DB2711240392FAA5174505A8E6FA60445495A73C3B8F804' USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.65)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl )
)
)'
 
CREATE DATABASE LINK AA CONNECT TO goldengate IDENTIFIED BY VALUES '06DE83525450FDFCB7219E0B1E5C01C053D2502C11E6FE94CF9DCE0C38BBDCBE8168AAE98DBACA2961CF29636D27F4101238AD84845120BFB204793FBC50A50C8DA8DF52BD785C29B3DC466D7F5FE9F2FBD79D9F85DCDA5604CAB36CB6AF7BFFBDF84F21A74F38C931291B5D9CF8F2B16BCD75EE7A35F246B8FAD62C' USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 135.192.49.136)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = zygl )
)
)'
 
CREATE DATABASE LINK AA1 CONNECT TO goldengate IDENTIFIED BY VALUES '0695C30AE2066EB6340C1A7CAB2CB5DAC253667405EF59B9E1ADD9E9E812D017B3129FC4B0A6D2B063D0B5EAC281A0B91B04FB1F25D1B4C35701654D7B67B1041D1BCF82E63166F480379B2C22DCE31F50554E0BF9B817F9C1B07340BB543856D3C78F412674C9B1354CC05CB03A5708D51EB9B597716C1DBB4A42CE8E30' USING 'orcl'
CREATE DATABASE LINK AA6 CONNECT TO OGG1 IDENTIFIED BY VALUES '06BA16CF75CF21FA683544FBB18E8FDC01BE754AA4DFA90CFB605658172AF45FB7CD9B01BF8872DA0CAC950EFF38D65BFC3A4EEB082822C6DBC680C5503D3A371217ADA11AB5724A1C4E0E69080C659ED0DBB784F7267174E526F975DCCD92116A3E37807DB2711240392FAA5174505A8E6FA60445495A73C3B8F804' USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.65)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl )
)
)'
 
 
6 ROWS selected.
 
SQL> DROP DATABASE link aa3;
 
DATABASE link dropped.
 
 
CREATE DATABASE LINK AA3 CONNECT TO OGG1 IDENTIFIED BY VALUES '06BA16CF75CF21FA683544FBB18E8FDC01BE754AA4DFA90CFB605658172AF45FB7CD9B01BF8872DA0CAC950EFF38D65BFC3A4EEB082822C6DBC680C5503D3A371217ADA11AB5724A1C4E0E69080C659ED0DBB784F7267174E526F975DCCD92116A3E37807DB2711240392FAA5174505A8E6FA60445495A73C3B8F804' USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.65)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl )
)
 8 )';
 
DATABASE link created.
 
SQL> SELECT * FROM tab@aa3;
 
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
OGG_CHAR TABLE
TEST1 TABLE

dbms_metaDATA.get_ddl

SELECT dbms_metaDATA.get_ddl('DB_LINK',a.db_link,a.owner) FROM dba_db_links a WHERE a.owner='TRAVEL';
 
 CREATE DATABASE LINK "AA"
 CONNECT TO "goldengate" IDENTIFIED BY VALUES '06DE83525450FDFCB7219E0B1E5C01C053D2502C11E6FE94CF9DCE0C38BBDCBE8168AAE98DBACA2961CF29636D27F4101238AD84845120BFB204793FBC50A50C8DA8DF52BD785C29B3DC466D7F5FE9F2FBD79D9F85DCDA5604CAB36CB6AF7BFFBDF84F21A74F38C931291B5D9CF8F2B16BCD75EE7A35F246B8FAD62C'
 USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 135.192.49.136)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = zygl )
)
)'
 
 
 CREATE DATABASE LINK "AA1"
 CONNECT TO "goldengate" IDENTIFIED BY VALUES '0695C30AE2066EB6340C1A7CAB2CB5DAC253667405EF59B9E1ADD9E9E812D017B3129FC4B0A6D2B063D0B5EAC281A0B91B04FB1F25D1B4C35701654D7B67B1041D1BCF82E63166F480379B2C22DCE31F50554E0BF9B817F9C1B07340BB543856D3C78F412674C9B1354CC05CB03A5708D51EB9B597716C1DBB4A42CE8E30'
 USING 'orcl'
 
 
 CREATE DATABASE LINK "AA5"
 CONNECT TO "OGG1" IDENTIFIED BY VALUES '06BA16CF75CF21FA683544FBB18E8FDC01BE754AA4DFA90CFB605658172AF45FB7CD9B01BF8872DA0CAC950EFF38D65BFC3A4EEB082822C6DBC680C5503D3A371217ADA11AB5724A1C4E0E69080C659ED0DBB784F7267174E526F975DCCD92116A3E37807DB2711240392FAA5174505A8E6FA60445495A73C3B8F804'
 USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.65)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl )
)
)'
 
 
 CREATE DATABASE LINK "TEST1"
 CONNECT TO "TRAVEL" IDENTIFIED BY VALUES '06307628FF3A73A9C177ED8D3E190256FCBED62E1EC3DD10847F3230BECF08C9298C6C1BC4ABF16E6A3843133008C4A1622CA2463929A98EE88F1829573454067B6F1861101DAA4049AAD974855562FC410CE5A0F3A2E91F6F945E88288AC0E66FA298D7570BF5A1CE70D522F6E53E07C12237112FC4BB723E4AA0CDA3A825DF'
 USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.65)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =orcl)
)
)'

原文地址：get_db_link_scripts, 感谢原作者分享。