ä¸. åè¨
è¿ä¸ªé®é¢æ¯ä¸ä¸ç¯æç« ãOracleè·¨æ°æ®åºå®ç°å®æ¶åæ¥æå®è¡¨ä¸çæ°æ®ãä¸ææé®é¢çè¿ä¸æ¥å»¶ä¼¸ãèèå°å¯¹æ°æ®çå®æ¶æ§è¦æ±æ¯è¾é«ï¼è®¾ç½®æå®æ¶åæ¥ï¼æç¹ä¸å¦¥ï¼éè¦æ¹åå级æ´æ¹ä¸ºå®æ¶åæ¥ã
ä¸é¢ä»ç»å°çæ¹å¼ï¼ä¸¥æ ¼æä¹ä¸è¯´ï¼å¹¶æ²¡æå®ç°å®æ¶åæ¥ãæ¯éè¿Oracleæ°æ®åºå建åä¹è¯+DBlinkçæ¹å¼ï¼æ¥å»ºç«è¿ç¨æ å°ãå¨æ¥çæ°æ®æ¶ï¼ç´æ¥å»è¿ç¨æ¥è¯¢æºåºä¸ç表ã
èæä¸ç´å
³æ³¨çæ¯å¦ä½è¿è¡åæ¥æ°æ®ï¼å´æ²¡æèèå°å¯ä»¥å»ºç«æ å°ãå¦å¤ï¼è¿æä¸ç¹å°±æ¯éè¦äº¤ä»£ï¼å°±æ¯åæ¥å°ç®æ æ°æ®åºä¸ç表ï¼ä¸è¿è¡å¢å æ¹æä½ï¼åªè¿è¡æ¥è¯¢çæä½ï¼è¿ä¹æ¯å¯ä»¥ä½¿ç¨åä¹è¯æ¹å¼è§£å³é®é¢çå
³é®ã
è¿ç§è§£å³æè·¯çåºç°ï¼å¤äºäºå¨CSDNé®çæ¶ï¼çå¿ç½åçåçï¼ä¸é¢å°±æ¯é®é¢è¯¦æ
æè¿°ã
äº. é®é¢æè¿°
æ两个Oracleæ°æ®åºï¼åå«å¸ç½®å¨ä¸åçæå¡å¨ä¸ï¼ç³»ç»å为windows2003ï¼
è¿éæä¸è¯´ææ¯ä¸ä¸ªä¸»æ°æ®åºåä»æ°æ®åºï¼
(1) 主æ°æ®åº:oracle_Aï¼
(2) ä»æ°æ®åº:oracle_Bï¼
å¨oracle_Aä¸æä¸ä¸ªè¡¨table_Aä¸oracle_Bä¸ç表table_Bç»æç¸åï¼
ææ¯å¤å¨oracle_Bï¼oracle_Aæ°æ®åºåé
ç»ææä¸ä¸ªè®¿é®oracle_A表table_Açç¨æ·ï¼è¯¥ç¨æ·åªæ¥ææ¥è¯¢çæéï¼
å¦å¤ï¼éè¦è¯´æçä¸ç¹ï¼å°±æ¯å¨oracle_Bå¤,åªé对table_B表è¿è¡æ¥è¯¢çæä½ï¼ä¸è¿è¡å
¶ä»å¢å æ¹çæä½ã
åºæ¯ä»ç»å®äºï¼æçé®é¢çæ¯,å¦ä½å¨oracle_Aä¸è¡¨table_Aåçååæ¶,å®æ¶æ´æ°åæ¥å°oracle_Bçtable_Bä¸?
æåæ¥çå¤çæ¹å¼:
éè¿å»ºç«è¿ç¨è¿æ¥DBLink+JOBå®æ¶ä»»å¡+åå¨è¿ç¨çæ¹å¼,å®ç°äºå®æ¶åæ¥æ´æ°,ä½ä¸è½åå°å®æ¶åæ¥ã
ä¸. éç¨åä¹è¯+DB_Linkçæ¹å¼ç»ææ¥éª¤
ä¹æ以è½å¤éæ©éç¨åä¹è¯çæ¹å¼ï¼å¤çè¿ä¸ªé®é¢ã主è¦è¿æ¯æºäºå¨é®é¢æè¿°ä¸æå°ä¸ä¸ªç¹ï¼é£å°±æ¯æ们åªéè¦å¯¹åæ¥åç表è¿è¡æ¥è¯¢æä½ãè¿ç¹æ¯ä½¿ç¨åä¹è¯æ¹å¼çéè¦è¦ç´ ã
ä¸é¢è¯¦ç»æ¨¡æä¸ä¸æ´ä¸ªå®éªæµè¯çè¿ç¨ï¼
ï¼1ï¼é¦å
å¨Oracle_A端å建ä¸ä¸ªå¯¹table_Aåªææ¥è¯¢åè½çç¨æ·
<1> å建ç¨æ·
sqlplus /nolog
conn /as sysdba;
create user username identified by password;
<2> æ¥çææçç¨æ·å表
ç¨æ·å建å®æå,æ¥çç¨æ·æ¯å¦å建æå
select * from all_users;
<3>æäºæé
为äºè½å¤ä¿è¯è½å¤ç»é,å¿
é¡»èµäºå¦ä¸æé
--æäºusernameç¨æ·å建sessionçæé,å³ç»éæé
grant create session to username;
--æäºusernameç¨æ·ä½¿ç¨è¡¨ç©ºé´çæé
grant unlimited tablespace to username;
--oracle对æé管çæ¯è¾ä¸¥è°¨,æ®éç¨æ·ä¹é´ä¹æ¯é»è®¤ä¸è½äºç¸è®¿é®ç,éè¦äºç¸ææ.
--å¦æscottç¨æ·è¦ææç»usernameç¨æ·æ¥çèªå·±çtest表çæé;
sqlplus scott/tiget@localhost:1521/orcl
--æäºusernameç¨æ·æ¥çæå®çæé
grant select on test to username;
--æ¤éæé
åºæ¬è¯æ³ågrant,å
³é®å为revoke;
ï¼2ï¼éªè¯ç¨æ·æ¯å¦å¯ä»¥æåç»å½ï¼å¹¶è¿è¡è®¿é®ææç表
--使ç¨sqlplusç»å½ï¼å¹¶è¿è¡æ¥è¯¢
sqlplus username/password@localhost:1521/orcl;
select * from scott.test;
注æï¼æ¥è¯¢è¡¨æ¶ï¼å¡å¿
带ä¸ç¨æ·åï¼è¯´ææ¯åªä¸ªç¨æ·ä¸ç表ã
ï¼3ï¼å建è¿ç¨è¿æ¥DB_Link
<1> å建è¿ç¨è¿æ¥ db_link
create public database link db32ãconnect to tianzhi_test identified by "tianzhi_test" using '192.168.56.6:1521/ORCL'
<2> æµè¯è¿ç¨è¿æ¥æ¯å¦æå
select * from tianzhi_smart.zh_item_news@db32;
ï¼4ï¼å¨Oracle_B端å建åä¹è¯
<1> 使ç¨sqlplusç»å½èªå·±çç¨æ·
sqlplus tianzhi_smart/tianzhi_smart@localhost:1521/orcl
<2> å建åä¹è¯
create or replace public synonym TEST1130 for scott.TEST@db32;
<3> æ¥è¯¢æµè¯
select * from TEST1130;
å¯ä»¥çå°è¿ä¸å¨Oracle_Aæºæ°æ®åºä¸æ¥å°çtable_A表ä¸çæ°æ®ä¸æ ·.
注æäºé¡¹:
å½è¿ç¨æ¥è¯¢çæ°æ®åºä¸å
å«BLOBå段æ¶,ä¼æ¥åºå¦ä¸é误.
ORA-22992: æ æ³ä½¿ç¨ä»è¿ç¨è¡¨éæ©ç LOB å®ä½å¨
å½åºç°è¿ä¸ªé误çæ¶åï¼é£æ¯å 为跨åºè¿æ¥æ¥è¯¢ä¸ç表ä¸åå¨BLOBç±»åçå段ï¼æ以ä¸å®è¦æ³¨æï¼ææ表ä¸åå¨blobç±»åå段ï¼
ä¸è½ç¨ select * from è¿æ¥ç表
ä¸è½å°blobç±»åçå段åºç°å¨èæ¬ä¸ã
å¦æè¿äºblobç±»åçå段ä¸å®è¦å¯¼è¿æ¥ï¼å¯ä»¥å
建ç«ä¸´æ¶è¡¨åæå
¥æ¬å°è¡¨ï¼æ¹æ³å¦ä¸.å¨pl/sqlä¸æ§è¡
第ä¸æ¥ 建临æ¶è¡¨
create global temporary table foo ( X BLOB )
on commit delete rows;
第äºæ¥ æå
¥æ¬å°è¡¨
insert into foo select blobcolumn from remoteTable@dl_remote ;
温馨提示:答案为网友推荐,仅供参考