oracle dblink µÄÓ¦ÓÃ
1¡¢ÓÃdblinkÁ´½Óoracle
£¨1£©Óëƽ̨Î޹صÄд·¨£º
create public database
link cdt connect to apps
identified by apps using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.205.100)(PORT = 1541))
)
(CONNECT_DATA =
(SERVICE_NAME = CDT)
)
)'
£¨2£©¿ÉÒÔ½«µ¥ÒýºÅÄÚµÄÄÚÈÝÓÃÒ»¸ö·þÎñÃû´úÌæ¡£¶ø½«ÆäÄÚÈÝдÔÚtnsname.oraÖУ¬ÕâÖÖд·¨ÓÐʱ²»³É¹¦¡£
2¡¢²Î¿¼ÈçÏÂÄÚÈÝ
Á©Ì¨²»Í¬µÄÊý¾Ý¿â·þÎñÆ÷£¬´Óһ̨Êý¾Ý¿â·þÎñÆ÷µÄÒ»¸öÓû§¶ÁÈ¡Áíһ̨Êý¾Ý¿â·þÎñÆ÷ϵÄij¸öÓû§µÄÊý¾Ý£¬Õâ¸öʱºò¿ÉÒÔʹÓÃdblink¡£
ÆäʵdblinkºÍÊý¾Ý¿âÖеÄview²î²»¶à£¬½¨dblinkµÄʱºòÐèÒªÖªµÀ´ý¶ÁÈ¡Êý¾Ý¿âµÄipµØÖ·£¬ssidÒÔ¼°Êý¾Ý¿âÓû§ÃûºÍÃÜÂë¡£
´´½¨¿ÉÒÔ²ÉÓÃÁ½ÖÖ·½Ê½£º
1¡¢ÒѾÅäÖñ¾µØ·þÎñ
create public database
link fwq12 connect to fzept
identified by neu using 'fjept'
CREATE DATABASE LINKÊý¾Ý¿âÁ´½ÓÃûCONNECT TO Óû§Ãû IDENTIFIED BY ÃÜÂë USING ‘±¾µØÅäÖõÄÊý¾ÝµÄʵÀýÃû’;
2¡¢Î´ÅäÖñ¾µØ·þÎñ
create database link linkfwq
connect to fzept identified by neu
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.142.202.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = fjept)
)
)';
host£½Êý¾Ý¿âµÄipµØÖ·£¬service_name£½Êý¾Ý¿âµÄssid¡£
ÆäʵÁ½ÖÖ·½·¨ÅäÖÃdblinkÊDz¶àµÄ£¬ÎÒ¸öÈ˸оõ»¹ÊǵڶþÖÖ·½·¨±È½ÏºÃ£¬ÕâÑù²»Êܱ¾µØ·þÎñµÄÓ°Ïì¡£
Êý¾Ý¿âÁ¬½Ó×Ö·û´®¿ÉÒÔÓÃNET8 EASY CONFIG»òÕßÖ±½ÓÐÞ¸ÄTNSNAMES.ORAÀﶨÒå.
Êý¾Ý¿â²ÎÊýglobal_name=trueʱҪÇóÊý¾Ý¿âÁ´½ÓÃû³Æ¸úÔ¶¶ËÊý¾Ý¿âÃû³ÆÒ»Ñù
Êý¾Ý¿âÈ«¾ÖÃû³Æ¿ÉÒÔÓÃÒÔÏÂÃüÁî²é³ö
SELECT * from GLOBAL_NAME;
²éѯԶ¶ËÊý¾Ý¿âÀïµÄ±í
SELECT …… from ±íÃû@Êý¾Ý¿âÁ´½ÓÃû;
²éѯ¡¢É¾³ýºÍ²åÈëÊý¾ÝºÍ²Ù×÷±¾µØµÄÊý¾Ý¿âÊÇÒ»ÑùµÄ£¬Ö»²»¹ý±íÃûÐèҪд³É“±íÃû@dblink·þÎñÆ÷”¶øÒÑ¡£
¸½´ø˵ÏÂͬÒå´Ê´´½¨:
CREATE SYNONYMͬÒå´ÊÃûFOR ±íÃû;
CREATE SYNONYMͬÒå´ÊÃûFOR ±íÃû@Êý¾Ý¿âÁ´½ÓÃû;
ɾ³ýdblink£ºDROP PUBLIC DATABASE LINK linkfwq¡£
Èç¹û´´½¨È«¾Ödblink£¬±ØÐëʹÓÃsystm»òsysÓû§£¬ÔÚdatabaseÇ°¼Ópublic¡£
3¡¢ÓÃdblinkÁ´½Ósqlserver;
²Î¿¼1£º
ͨ¹ýdblink·ÃÎÊsqlserverÊý¾Ý¿â
ͨÓÃÍø¹Ø
OracleÒì¹¹·þÎñÊÇ°üº¬ÔÚOracleÊý¾Ý¿âÖеÄÒ»¸öÄ£¿é£¬Í¨¹ýʹÓÃ͸Ã÷Íø¹Ø(Transparent Gatewa
Ïà¹ØÎĵµ£º
DML:Data Manipulation Language Êý¾Ý²Ù×÷ÓïÑÔ
°üÀ¨£ºCRUD
1. insertÓï¾ä
(1) ´ÓÆäËü±íÖи´ÖÆÊý¾Ý,ʵÏÖ·½·¨:ÔÚinsert Óï¾äÖмÓÈë²éѯÓï¾ä
insert into sales_reps(id,name,salary,commission_pct) select employee_id,last_name,salary,commission_pct
from employees where job_id like '%rep';
(2) up ......
1:pfileºÍspfile
ÔÚ9i֮ǰ£¬²ÎÊýÎļþÖ»ÓÐÒ»ÖÖ£¬ËüÊÇÎı¾¸ñʽµÄ£¬³ÆΪpfile£¬ÔÚ9i¼°ÒÔºóµÄ°æ±¾ÖУ¬ÐÂÔöÁË·þÎñÆ÷²ÎÊýÎļþ,³ÆΪspfile,ËüÊǶþ½øÖƸñʽµÄ¡£ÕâÁ½ÖÖ²ÎÊýÎļþ¶¼ÊÇÓÃÀ´´æ´¢²Î ÊýÅäÖÃÒÔ¹©oracle¶ÁÈ¡µÄ£¬µ«Ò²Óв»Í¬µã£¬×¢ÒâÒÔϼ¸µã£º
1)pfileÊÇÎı¾Îļþ£¬spfileÊǶþ½øÖÆÎļþ£»
2)¶ÔÓÚ²ÎÊýµÄÅäÖã¬pfile¿ÉÒÔÖ±½ÓÒÔÎ ......
¸ôÀ뼶±ð£¨isoation level£©
¸ôÀ뼶±ð¶¨ÒåÁËÊÂÎñÓëÊÂÎñÖ®¼äµÄ¸ôÀë³Ì¶È¡£
¸ôÀ뼶±ðÓë²¢·¢ÐÔÊÇ»¥ÎªÃ¬¶ÜµÄ£º¸ôÀë³Ì¶ÈÔ½¸ß£¬Êý¾Ý¿âµÄ²¢·¢ÐÔÔ½²î£»¸ôÀë³Ì¶ÈÔ½µÍ£¬Êý¾Ý¿âµÄ²¢·¢ÐÔÔ½ºÃ¡£
ANSI/ISO SQ92±ê×¼¶¨ÒåÁËһЩÊý¾Ý¿â²Ù×÷µÄ¸ôÀ뼶±ð£º
δÌá½»¶Á£¨read uncommitted£©
Ìá½»¶Á£¨read committed£© &n ......
1. ´´½¨ÊÓͼ£º
CREATE OR REPLACE VIEW SM_V_UNIT_AUTH AS
SELECT T2.UNIT_ID,
T2.SUPER_UNIT_ID,
T1.AUTH_ID,
T1.AUTH_NAME,
T1.A ......
Union£¬¶ÔÁ½¸ö½á¹û¼¯½øÐв¢¼¯²Ù×÷£¬²»°üÀ¨Öظ´ÐУ¬Í¬Ê±½øÐÐĬÈϹæÔòµÄÅÅÐò£»
Union All£¬¶ÔÁ½¸ö½á¹û¼¯½øÐв¢¼¯²Ù×÷£¬°üÀ¨Öظ´ÐУ¬²»½øÐÐÅÅÐò£»
Intersect£¬¶ÔÁ½¸ö½á¹û¼¯½øÐн»¼¯²Ù×÷£¬²»°üÀ¨Öظ´ÐУ¬Í¬Ê±½øÐÐĬÈϹæÔòµÄÅÅÐò£»
Minus£¬¶ÔÁ½¸ö½á¹û¼¯½øÐвî²Ù×÷£¬²»°üÀ¨Öظ´ÐУ¬Í¬Ê±½øÐÐĬÈϹæÔòµÄÅÅÐò¡£
¿ÉÒÔÔÚ×î ......