Oracle DB Link½éÉÜ
1¡£»ù±¾¸ÅÄ
Êý¾Ý¿âÁ¬½Ó´®Ö÷ÒªÓÃÓÚ½¨Á¢¶ÔÔ¶³ÌÊý¾Ý¿âµÄ·ÃÎÊ·½·¨£¬¿ÉÒÔÖ±½Ó¶ÁÈ¡Ô¶³ÌOracleµÄÊý¾Ý£¬»òÕßÖ±½ÓÐ޸ġ£Êý¾Ý¿âÁ¬½Ó´®¿ÉÒÔÊǹ«ÓÃÁ¬½ÓPUBLIC»òÕß˽ÓÐÁ¬½ÓPRIVATE¡£ÕâÒ»µãºÍͬÒå´ÊºÜÏàÏñ¡£
1¡£´´½¨Óï·¨£º
CREATE DATABASE LINK TEST CONNECT TO USERNAME IDENTIFIED BY PASSWORD
USING 'CONNECT_STRING';
½âÊÍ£ºTESTÊÇÊý¾Ý¿âÁ¬½Ó´®µÄÃû×Ö¡£ÒÔºó¾Íͨ¹ýÕâ¸öÃû×ÖÀ´½øÐе÷ÓÃÔ¶³ÌÊý¾Ý¿âµÄÄÚÈÝ¡£
USERNAMEÊÇÓÃÀ´Á¬½Óµ½Ô¶³ÌÊý¾Ý¿âµÄºÏ·¨OracleÓû§Ãû¡£PASSWORDΪ¸ÃÓû§Á¬½Óµ½OracleʱºòµÄºÏ·¨ÃÜÂë¡£
CONNECT_STRINGΪ¸ÃOracleÊý¾Ý¿âËùÔÚµÄÖ÷»úÉϵÄtnsnames.oraÎļþÀï±ß¶¨ÒåµÄÊý¾Ý¿âÁ¬½Ó´®¡£
2¡£Ê¹Ó÷½·¨£º
SELECT COUNT(*) from TABLE_NAME@DB_LINK WHERE WHERE_CLAUSE;//²éÔÄÔ¶³ÌÊý¾Ý¿âµÄÄÚÈÝ¡£
UPDATE
TEST.TEST@DB_LINK
SET SO_NBR=NEW_SO_NBR
WHERE WHERE_CLAUSE;//
ÐÞ¸ÄÔ¶³ÌÊý¾Ý¿âµÄÄÚÈÝ¡£
ÔÚʵ¼ÊʹÓùý³ÌÖУ¬»¹¿ÉÒÔͨ¹ýΪÕâ¸öÔ¶³Ì±í½¨Á¢Ò»¸öͬÒå´ÊÀ´½øÒ»²½Ôö¼Ó͸Ã÷ÐÔ£¬Ê¹¸ÃÊý¾Ý¿âÁ¬½Ó´®¶ÔÓÚ³ÌÐòºÍ¿ª·¢ÈËÔ±À´½²Íêȫ͸Ã÷¡£
CREATE SYNONYM TABLE_NAME FOR TABLE_NAME@DB_LINK;
3¡£Êý¾Ý¿âÁ¬½ÓµÄ¹ÜÀí£º
1¡£ÈçºÎÖªµÀµ±Ç°Óû§¡¢µ±Ç°ÏµÍ³ÖÐÓÐÄÄЩÊý¾Ý¿âÁ¬½Ó´®£¿
SELECT * from USER_DB_LINKS;
Õâ¸ö²éѯ»á¸ø³öµ±Ç°Óû§µÄÊý¾Ý¿âÁª½ÓµÄÃû×Ö¡¢Áª½ÓÓû§Ãû¡¢Áª½ÓÃÜÂë¡¢ÒªÁ¬½ÓµÄÖ÷»úÒÔ¼°´´½¨Ê±ÆÚ¡£
SELECT * from DBA_DB_LINKS;
Õâ¸ö²éѯ»á¸ø³öµ±Ç°ÏµÍ³ÖÐËùÓеÄÊý¾Ý¿âÁª½ÓµÄ´´½¨Óû§¡¢Áª½ÓÓû§Ãû¡¢ÒªÁ¬½ÓµÄÖ÷»úÒÔ¼°´´½¨Ê±ÆÚ¡£
SELECT * from V$DBLINK;
Õâ¸ö²éѯ»á¸ø³öµ±Ç°´ò¿ªµÄÊý¾Ý¿âÁª½Ó¡£
Ïà¹ØÎĵµ£º
½ñÌìÉÏÎç²âÊÔÒ»¸ö·ÃÎÊORACLEµÄc++À࣬ÎĵµÉÏ˵Á¬½Ó×Ö·û´®µÄ¸ñʽΪ"Óû§Ãû/¿ÚÁî@Á¬½ÓÃû"£¬ÎÒ²»ÊÇÌ«Ã÷°×Á¬½ÓÃûµ½µ×ΪºÎÎÏÈÓÃIPµØÖ·ÊÔÁËÊÔ
£¬×ÜÊDZ¨´í£¬ËµÎÞ·¨½âÎöµÄÁ¬½Ó±êʶ·û£¬ºóÀ´ÔÚÍøÉϲéÁË°ëÌ죬¿´µ½ÓиöÈË˵Á¬½ÓÃû¾ÍÊÇ$(ORACLE_HOME)/network/admin/tnsnames.oraÀﶨÒåµÄÊý¾Ý¿âÁ¬½ÓµÄÃû³Æ£¬ÊÔÁËһϣ¬¹ûÈ»Èç´Ë¡£ ......
1¸öʵÀý
create table tjob2(tt date);
´´½¨Ò»¸ö´æ´¢¹ý³Ì
create or replace procedure t26 is
begin
insert into tjob2 values(sysdate);
commit;
end t26;
´´½¨job£¬Ã¿·ÖÖÓÖ´ÐÐÒ»´Î
SQL> declare
2 tjob number;
3 begin
4 sys.dbms_jo ......
Êý¾Ý¿âÃû(DB_NAME)¡¢ÊµÀýÃû(Instance_name)¡¢ÒÔ¼°²Ù×÷ϵͳ»·¾³±äÁ¿(ORACLE_SID)
ÔÚORACLE7¡¢8Êý¾Ý¿âÖÐÖ»ÓÐÊý¾Ý¿âÃû(db_name)ºÍÊý¾Ý¿âʵÀýÃû(instance_name)¡£ÔÚORACLE8i¡¢9iÖгöÏÖÁËеIJÎÊý£¬¼´Êý¾Ý¿âÓòÃû(db_domain)¡¢·þÎñÃû(service_name)¡¢ÒÔ¼°²Ù×÷ϵͳ»·¾³±äÁ¿(ORACLE_SID)¡£ÕâЩ¶¼´æÔÚÓÚͬһ¸öÊý¾Ý¿âÖеıêʶ£¬ÓÃÓÚÇ ......
1.½¨±í
CREATE TABLE qq(
qq_num integer
);
2.²åÈëÊý¾Ý
²åÈëÊý¾ÝµÄ·½·¨ÓкܶàÖÖ£¬µ«ÊÇÒª²åÈë3ÒÚÊý¾Ý£¬±ØÐëÒªÓиßЧµÄËã·¨¡£ÔÚ²åÈëÊý¾ÝʱÎÒÓÃÁË×µÄ°ì·¨£¬²ÉÓÃPL/SQLÀûÓÃÁ÷³Ì¿ØÖƵÄLOOPÓï¾ä²åÈ롣ͬʱÎÒÒ²¿´ÁËÍøÉϵIJåÈëЧÂʵÄÓÅ»¯£¬ÔõÑùÀûÓýÏÉÙµÄʱ¼äÉú³É½Ï¶àµÄ²âÊÔÊý¾Ý¡£¸ø ......
spool d:\deletetb.sql;
select 'delete ' || table_name || ' where to_char(col,'||'''yyyy'''||')='||'''2007'''||';'
from user_tables
where table_name in (select 'HS_' || lpad(rownum, '2', '0')
from dual
  ......