get_db_link_scripts
程序员文章站
2024-02-13 22:35:22
...
在用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 '06BA16CF75CF21FA683544FBB18E8FDC01BE754AA4DFA90CFB605658172AF45FB7CD9B01BF898872DA0CAC950EFF38D65BFC3A4EEB08286422C6DBC680C5503D3A371217ADA11AB5724A1C4E0E69080C659ED064D89BB784F7267174E526F975DCCD92116A3E37807DB2711240392FAA5174505A8E6FA60445495A73C3B8F804' 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 '06BA16CF75CF21FA683544FBB18E8FDC01BE754AA4DFA90CFB605658172AF45FB7CD9B01BF898872DA0CAC950EFF38D65BFC3A4EEB08286422C6DBC680C5503D3A371217ADA11AB5724A1C4E0E69080C659ED064D89BB784F7267174E526F975DCCD92116A3E37807DB2711240392FAA5174505A8E6FA60445495A73C3B8F804' 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 '06DE83525450FDFCB7219E0B1E895C01C053D2502C11E6FE94CF9DCE0C38BBDCBE8168AAE98DBACA2961CF29636D27F416401238AD84845120BFB204793FBC50A50C8DA8DF52BD785C29B3DC466D897F5FE9F2FBD79D9F85DCDA5604CAB36CB6AF7BFFBDF84F21A74F3898C931291B5D9CF8F2B16BCD75EE7A35F246B8FAD62C' 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 '0695C30AE2066EB6340C1A7CAB2CB5DAC253667405EF59B9E1ADD9E9E812D017B3129FC4B0A6D2B063D0B5EAC281A0B91B04FB1F25D1B4C35701654D7B67B1041D1BCF82E63166F480379B2C22DCE31F50554E0BF9B817F9C1B07340BB54385689D3C78F412674C9B1354CC05CB03A5708D51EB9B597716C1DBB4A42CE8E3089' USING 'orcl'
CREATE DATABASE LINK AA6 CONNECT TO OGG1 IDENTIFIED BY VALUES '06BA16CF75CF21FA683544FBB18E8FDC01BE754AA4DFA90CFB605658172AF45FB7CD9B01BF898872DA0CAC950EFF38D65BFC3A4EEB08286422C6DBC680C5503D3A371217ADA11AB5724A1C4E0E69080C659ED064D89BB784F7267174E526F975DCCD92116A3E37807DB2711240392FAA5174505A8E6FA60445495A73C3B8F804' 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 '06BA16CF75CF21FA683544FBB18E8FDC01BE754AA4DFA90CFB605658172AF45FB7CD9B01BF898872DA0CAC950EFF38D65BFC3A4EEB08286422C6DBC680C5503D3A371217ADA11AB5724A1C4E0E69080C659ED064D89BB784F7267174E526F975DCCD92116A3E37807DB2711240392FAA5174505A8E6FA60445495A73C3B8F804' 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
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 '06DE83525450FDFCB7219E0B1E895C01C053D2502C11E6FE94CF9DCE0C38BBDCBE8168AAE98DBACA2961CF29636D27F416401238AD84845120BFB204793FBC50A50C8DA8DF52BD785C29B3DC466D897F5FE9F2FBD79D9F85DCDA5604CAB36CB6AF7BFFBDF84F21A74F3898C931291B5D9CF8F2B16BCD75EE7A35F246B8FAD62C'
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 '0695C30AE2066EB6340C1A7CAB2CB5DAC253667405EF59B9E1ADD9E9E812D017B3129FC4B0A6D2B063D0B5EAC281A0B91B04FB1F25D1B4C35701654D7B67B1041D1BCF82E63166F480379B2C22DCE31F50554E0BF9B817F9C1B07340BB54385689D3C78F412674C9B1354CC05CB03A5708D51EB9B597716C1DBB4A42CE8E3089'
USING 'orcl'
CREATE DATABASE LINK "AA5"
CONNECT TO "OGG1" IDENTIFIED BY VALUES '06BA16CF75CF21FA683544FBB18E8FDC01BE754AA4DFA90CFB605658172AF45FB7CD9B01BF898872DA0CAC950EFF38D65BFC3A4EEB08286422C6DBC680C5503D3A371217ADA11AB5724A1C4E0E69080C659ED064D89BB784F7267174E526F975DCCD92116A3E37807DB2711240392FAA5174505A8E6FA60445495A73C3B8F804'
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, 感谢原作者分享。