dblink 璁块棶杩滅▼鏁版嵁搴?/span>
褰撶敤鎴疯璺ㄦ湰鍦版暟鎹簱锛岃闂彟澶栦竴涓暟鎹簱琛ㄤ腑鐨勬暟鎹椂锛屾湰鍦版暟鎹簱涓繀椤诲垱寤轰簡杩滅▼鏁版嵁搴撶殑dblink,閫氳繃dblink鏈湴鏁版嵁搴撳彲浠ュ儚璁块棶鏈湴鏁版嵁搴撲竴鏍疯闂繙绋嬫暟鎹簱琛ㄤ腑鐨勬暟鎹€備笅闈㈣浠嬬粛濡備綍鍦ㄦ湰鍦版暟鎹簱涓垱寤篸blink.
銆€銆€鍒涘缓dblink涓€鑸湁涓ょ鏂瑰紡锛屼笉杩囧湪鍒涘缓dblink涔嬪墠鐢ㄦ埛蹇呴』鏈夊垱寤篸blink鐨勬潈闄愩€傛兂鐭ラ亾鏈夊叧dblink鐨勬潈闄愶紝浠ys鐢ㄦ埛鐧诲綍鍒版湰鍦版暟鎹簱锛?/p>
銆€銆€select * from user_sys_privs t
銆€銆€where t.privilege like upper('%link%');
銆€銆€1 SYS CREATE DATABASE LINK NO
銆€銆€2 SYS DROP PUBLIC DATABASE LINK NO
銆€銆€3 SYS CREATE PUBLIC DATABASE LINK NO
銆€銆€鍙互鐪嬪嚭鍦ㄦ暟鎹簱涓璬blink鏈変笁绉嶆潈闄怌REATE DATABASE LINK锛堟墍鍒涘缓鐨刣blink鍙兘鏄垱寤鸿€呰兘浣跨敤锛屽埆鐨勭敤鎴蜂娇鐢ㄤ笉浜嗭級锛孋REATE PUBLIC DATABASE LINK锛坧ublic琛ㄧず鎵€鍒涘缓鐨刣blink鎵€鏈夌敤鎴烽兘鍙互浣跨敤锛夛紝DROP PUBLIC DATABASE LINK銆?/p>
銆€銆€鍦╯ys鐢ㄦ埛涓嬶紝鎶奀REATE PUBLIC DATABASE LINK锛孌ROP PUBLIC DATABASE LINK鏉冮檺鎺堜簣缁欎綘鐨勭敤鎴?/p>
銆€銆€grant CREATE PUBLIC DATABASE LINK锛孌ROP PUBLIC DATABASE LINK to scott;
銆€銆€鐒跺悗浠cott鐢ㄦ埛鐧诲綍鏈湴鏁版嵁搴?/p>
銆€銆€1.鍒涘缓dblink鐨勭涓€绉嶆柟寮忥紝鏄湪鏈湴鏁版嵁搴搕nsnames.ora鏂囦欢涓厤缃簡瑕佽繙绋嬭闂殑鏁版嵁搴撱€?/p>
銆€銆€create public database link
銆€銆€to_bylw connect to scott identified by tiger using 'bylw';
銆€銆€鍏朵腑to_bylw鏄綘鍒涘缓鐨刣blink鍚嶅瓧锛宐ylw鏄繙绋嬫暟鎹簱鐨勫疄渚嬪悕锛宻cott/tiger鏄櫥褰曞埌杩滅▼鏁版嵁搴撶殑鐢ㄦ埛/瀵嗙爜銆傜劧鍚庡湪鏈湴鏁版嵁搴撲腑閫氳繃dblink璁块棶杩滅▼鏁版嵁搴?bylw'涓璼cott.tb_test琛?sql璇彞濡備笅鎵€绀?/p>
銆€銆€select * from scott.tb_test@to_bylw;
銆€銆€2.鍒涘缓dblink鐨勭浜岀鏂瑰紡锛屾槸鍦ㄦ湰鍦版暟鎹簱tnsnames.ora鏂囦欢涓病鏈夐厤缃璁块棶鐨勮繙绋嬫暟鎹簱锛?/p>
銆€銆€create database link to_test
銆€銆€connect to scott identified by tiger
銆€銆€using '(DESCRIPTION =
銆€銆€(ADDRESS_LIST =
銆€銆€(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
銆€銆€)
銆€銆€(CONNECT_DATA =
銆€銆€(SERVER = DEDICATED)
銆€銆€(SERVICE_NAME = bylw)
銆€銆€)
銆€銆€)';
銆€銆€绗簩绉嶆槸鎶婄涓€绉嶉厤缃湪tnsnames.ora鏂囦欢涓殑淇℃伅锛岀洿鎺ユ斁鍦ㄥ垱寤篸blink璇彞鍚庨潰銆傜涓€绉嶆儏鍐祎nsnames.ora鏂囦欢涓俊鎭涓嬶細
銆€銆€bylw =
銆€銆€(DESCRIPTION =
銆€銆€(ADDRESS_LIST =
銆€銆€(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
銆€銆€)
銆€銆€(CONNECT_DATA =
銆€銆€(SERVER = DEDICATED)
銆€銆€(SERVICE_NAME = bylw)
銆€銆€)
銆€銆€)
閫夋嫨浣跨敤绗簩绉嶆洿瀹规槗鎴愬姛銆?/p>
1銆伮?鍦ㄦ湰鍦?oracle鍜宲hd涓绘湇鍔″櫒)寤虹珛涓€涓狾racle鐨勫鎴风杩炴帴sxyuniformance锛岀敤浜庤繛鎺ヨ繙绋嬬鏈哄櫒鐨刼racle鏁版嵁搴撱€?/p>
聽
鍦ㄨ繙绋嬬鏈哄櫒鐨勬暟鎹簱涓紝寤虹珛涓€涓〃鐢ㄤ簬娴嬭瘯锛屽垱寤篟TDB_TAG琛ㄣ€?/p>
create table uniformance.RTDB_TAG
(
聽 聽聽聽聽聽聽聽聽NAME VARCHAR2(30) not null,
聽 聽聽聽聽聽聽聽聽DES VARCHAR2(60),
聽聽聽聽聽聽聽聽 聽UNIT VARCHAR2(15),
聽聽聽聽聽聽聽聽 聽HI聽聽 VARCHAR2(20),
聽聽聽 聽聽聽聽聽聽LO聽聽 VARCHAR2(10)
)
鍦ㄥ叾涓鍔犱竴鏉¤褰曪細
insert into uniformance.RTDB_TAG (NAME, DES, UNIT, HI, LO)
values ('TAG1', 'TAG TEST1', 'M', '10000', '-100');
聽
聽
2銆伮?鍦ㄦ湰鍦皁ralce鐧诲綍PLSQL锛屽垱寤鸿繙绋嬫暟鎹簱绔殑杩炴帴dblink
聽
create database link dblink connect to uniformance identified by聽 uniformance using 鈥榮xyuniformance鈥?
聽
dblink:database link鐨勫悕绉?/p>
uniformance锛氳繙绋嬬oracle鏁版嵁搴撶殑鐧诲綍鍚?/p>
uniformance锛氳繙绋嬬oracle鏁版嵁搴撶殑鐧诲綍瀵嗙爜
sxyuniformance锛氭湰鍦板缓绔嬬殑杩炴帴鍒拌繙绋嬬鐨勬湇鍔″悕绉?/p>
聽
3銆伮?娴嬭瘯杩炴帴鏄惁鎴愬姛
聽
聽聽 select * from dual@dblink
聽 聽濡傛灉鏈夌粨鏋滆繑鍥炲垯琛ㄧず杩炴帴鎴愬姛浜嗐€?/p>
聽
聽
4銆佸湪鏈湴鏁版嵁搴撲腑鏌ヨ宸茬粡寤虹珛鐨勮繙绋嬭繛鎺ュ悕锛?/p>
聽
聽聽 SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
聽聽聽 OWNER聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽OBJECT_NAME
聽聽聽Public聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 dblink
聽
5銆佽嚦姝わ紝鍦ㄦ湰鍦皁racle鍒涘缓涓€涓狣BLINK锛岀敤浜庤繛鎺ュ埌杩滅▼绔殑鏁版嵁搴撲笂锛?/p>
鍦ㄦ湰鍦扮殑鏁版嵁搴撲腑鏌ヨ杩滅▼绔暟鎹簱涓〃鐨勪俊鎭細
SQL> select * from RTDB_TAG@dblink
鏌ヨ缁撴灉娴嬭瘯鏄惁鎴愬姛
聽6.----濡傛灉 ssh聽 妗嗘灦寮€鍙戣繃绋嬩腑瑕佺敤鍒板彟涓€涓?oracle 鏁版嵁搴撲腑鐨勮〃锛屼絾鏄?hibernate 涓嶈兘鐩存帴瀵?dblink 涓殑琛ㄨ繘琛屾槧灏勫鑷存棤娉曚娇鐢?hibernate 鍒涘缓 pojo 绫伙紝瑙ｅ喅鐨勫姙娉曟
