|
IOTN :: Field Book :: ORACLE
|
ORACLE°ú MSSQL°£ÀÇ DBLINK ¹æ¹ý
|
ÃÖ±æÈ£
[LIST]
|
2008-09-19 08:04:51, Á¶È¸ : 65,404 |
ORACLE°ú MSSQL°£ÀÇ DBLINK ¹æ¹ý
Oracle_Transaparent Gateway for Microsoft MSSQL
===================================================================================================
¿À¶óŬ Á¦Ç°±º Áß¿¡ Oracle_Transaparent Gateway for Microsoft MSSQL ¶ó´Â °Ô ÀÖ½À´Ï´Ù.
¿À¶óŬ Enterprise EditionÀÌ ¼³Ä¡µÈ »óÅ¿¡¼ º°µµ ±¸¸ÅÇؾßÇÏ´Â Á¦Ç°ÀÔ´Ï´Ù¸¸ À̸§ÀÌ ÀǹÌÇϵíÀÌ À̱âÁ¾ DB¿Í ¿¬°á½Ã ¸Å¿ì ¶Ù¾î³ ¼º´ÉÀ» º¸¿©ÁÝ´Ï´Ù.
MS SQL¿¡¼ ORACLEÀ» ¿¬°áÇÒ¶§ LINKED SERVER ±â¼úÀÌ »ç¿ëµË´Ï´Ù. ODBC¸¦ ±â¹ÝÀ¸·Î ÇÏ°í ÀÖ°í ¹«·á·Î ÀÌ¿ëÇÒ ¼ö ÀÖÁö¸¸ OLTP ¸ñÀûÀ¸·Î »ç¿ëÇϱ⿡´Â ºÎÀûÇÕ´Ï´Ù.
¾Æ·¡ÀÇ ³»¿ëÀ¸·Î TEST¸¦ Çغ¸½Ã±â ¹Ù¶ø´Ï´Ù.
1. Oracle Enterprise EditionÀ» Windows PlatformÀ» °®´Â Machine¿¡ ¼³Ä¡ÇÑ´Ù.
2. ¼³Ä¡ Option¿¡¼ Transparent Gateway for Microsoft SQL Server OptionÀ» Ãß°¡ ¼³Ä¡ÇÑ´Ù.
3. ¿¬°áÇÏ°íÀÚ ÇÏ´Â MS SQL ServerÀÇ IP Address¿Í HostnameÀ» lmhosts ÆÄÀÏ¿¡ µî·ÏÇϰųª WINS Server¸¦ ÅëÇØ µî·ÏÇÑ´Ù.
4. $ORACLE_HOME\tg4msql\admin µð·ºÅ丮 ¾Æ·¡ÀÇ inittg4msql.ora ÆÄÀÏÀ» initSID.ora ·Î º¹»çÇÑ´Ù. (SID´Â »ç¿ëÀÚ°¡ MS SQL Serverº°·Î ÁöÁ¤ÇÒ ¼ö ÀÖ´Ù.)
5. ½Å±Ô·Î »ý¼ºÇÑ initSID.ora ÆÄÀÏÀ» Open ÈÄ HS_FDS_CONNECT_INFO °ªÀ» <MS SQL Server ¼³Ä¡ Machin À̸§>.<Database À̸§> À» ÁöÁ¤ÇÑ´Ù. ´Ü, MS SQL Server°¡ ¼³Ä¡µÈ MachineÀÇ °æ¿ì Host À̸§À¸·Î ÀÔ·ÂÇϵµ·Ï ÇÑ´Ù. IP Address´Â »ç¿ëÇÏÁö ¾Ê´Â´Ù.
6. $ORACLE_HOME\network\admin µð·ºÅ丮 ¾Æ·¡ÀÇ listener.ora ÆÄÀÏÀ» Open ÈÄ ´ÙÀ½°ú °°Àº ³»¿ëÀ» Ãß°¡ÇÑ´Ù.
(SID_DESC =
(SID_NAME = SID)
(ORACLE_HOME = $ORACLE_HOME)
(PROGRAM = tg4msql)
)
7. $ORACLE_HOME\network\admin µð·ºÅ丮 ¾Æ·¡ÀÇ tnsnames.ora ÆÄÀÏÀ» Open ÈÄ ´ÙÀ½°ú °°Àº ³»¿ëÀ» Ãß°¡ÇÑ´Ù.
SID =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Transparent Gateway°¡ ¼³Ä¡µÈ MachineÀÇ IP Address)(PORT = Listener¿¡¼ »ç¿ëÇÏ´Â Port¹øÈ£))
(CONNECT_DATA =
(SID = SID))
(HS = OK)
)
8. Datbase Link ¶Ç´Â Public Database Link¸¦ »ý¼ºÇÒ ¼ö ÀÖ´Â ±ÇÇÑÀÌ ÀÖ´Â »ç¿ëÀÚ·Î LoginÇÑ ÈÄ ´ÙÀ½°ú °°Àº ¹æ½ÄÀ¸·Î Database Link¸¦ »ý¼ºÇÑ´Ù. (¿©±â¼´Â Public Database Link¸¦ »ý¼ºÇÏ¿´´Ù.)
create public database link »ç¿ëÀÚ°¡ ½Äº°ÇÏ°íÀÚ ÇÏ´Â À̸§ connect to sa identified by simsnt using 'TNSNAMES.ORA¿¡¼ ÁöÁ¤ÇÑ À̸§';
9. Database Linkó·³ »ç¿ëÇÑ´Ù.
http://blog.naver.com/addibuddi/47467768
MS-SQLÀÇ Open Query
===================================================================================================
http://www.en-core.com/bin/main/module/solution/admin_view.asp?solution_code=&searchString=&column=&board_id=solution&state=view&article_id=19469&location=&page_num=1&group_id=19570&direction=n&step=0
SQL Server¿¡¼ Oracle table QUERYÇϱâ
SQL ServerÀÇ Transact-SQL¹®¿¡¼´Â SQL Server¿Ü ¾î¶² OLE DB data source¿¡ ´ëÇؼµµ Á¢±ÙÀÌ °¡´ÉÇÏ´Ù. À̸¦ À§Çؼ´Â Linked server name (LINKED SERVER)À̳ª Ad hoc connector name (OPENROWSET) µÎ °¡Áö ¹æ½ÄÀ» Áö¿øÇÑ´Ù. Linked server nameÀº ÀÚÁÖ »ç¿ëÇÏ´Â °æ¿ì¿¡Connection¿¡ ´ëÇÑ Á¤º¸¸¦ ¹Ì¸® µî·ÏÇؼ Linked Server¸¦ »ý¼ºÇØ ³õ°í »ç¿ëÇÏ´Â ¹æ½ÄÀ̸ç, Ad hoc connector nameÀº ÀÚÁÖ »ç¿ëÇÏÁö ¾Ê´Â °æ¿ì¿¡ »ç¿ëÇϸç connection Á¤º¸¸¦ ¸Å¹ø Á¦°øÇÏ´Â ¹æ½ÄÀÌ´Ù. ÀÌÁß¿¡¼ Linked server name¹æ½ÄÀ» ÅëÇؼ Oracle(Å×½ºÆ®¿¡´Â 8.1.7 VersionÀÌ »ç¿ëµÇ¾ú´Ù.)¿¡ Á¸ÀçÇÏ´Â Table¿¡ DMLÀ» ¼öÇàÇÏ°íÀÚ ÇÒ °æ¿ì ¼³Á¤¹æ¹ý°ú ÁÖÀÇÁ¡¿¡ ´ëÇؼ ¾Ë¾Æº¸°íÀÚ ÇÑ´Ù.
1. Oracle Client ¼³Ä¡ ¹× Oracle Net Service Name µî·Ï Çϱâ
Oracle¿¡ Á¢¼ÓÇϱâ À§Çؼ´Â Oracle client¸¦ ¼³Ä¡ÇÏ°í Á¢¼ÓÇÏ°íÀÚ ÇÏ´Â Oracle DB¿¡ ´ëÇÑ Á¤º¸¸¦ µî·ÏÇØÁà¾ß ÇÑ´Ù. ÀÌ °úÁ¤¿¡¼ ODBC Data Source Name°ú ºñ½ÁÇÑ ¿ªÇÒÀ» ÇÏ´ÂOracle Net Service NameÀ» µî·ÏÇÏ°Ô µÈ´Ù. ¼³Ä¡ ¹× ODBC Data Source Nameµî·Ï¿¡ ´ëÇÑ ¼³¸íÀº Oracle client ¼³Ä¡ °¡À̵带 Âü°í Çϱ⠹ٶõ´Ù.
2. Linked server µî·ÏÇϱâ
Linked server name¹æ½ÄÀ¸·Î ÀÛ¾÷À» ¼öÇàÇϱâ À§Çؼ´Â Enterprise Manager¿¡¼ Linked server¸¦ ¼³Á¤Çϰųª T-SQL¿¡¼ Sp_addLinkedServer, Sp_addLinkedSrvLogin, Sp_serverOption, Sp_dropServer µîÀ» ÀÌ¿ëÇؼ ¼³Á¤ÇÏ¸é µÈ´Ù. °¢ SP¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ¼³¸íÀº BOL(¿Â¶óÀμ³¸í¼)¸¦ ÂüÁ¶Çϱ⠹ٶó¸ç, ¾Æ·¡´Â Linked server¸¦ »ý¼ºÇÏ´Â °£´ÜÇÑ ¿¹ÀÌ´Ù.
(1) Linked server »ý¼ºÇϱâ
sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog' ]
¿¹) EXEC sp_addlinkedserver 'JYOra817', 'Oracle', 'MSDAORA', 'JY817'
- JY817À̶ó´Â Oracle Net Service NameÀ¸·Î µî·ÏµÈ Oracleµ¥ÀÌÅͺ£À̽º¸¦ JYOra817À̶ó´Â À̸§ÀÇ Linked server·Î µî·ÏÇÑ´Ù.
(2) Login »ý¼ºÇϱâ
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'useself' ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
¿¹) EXEC sp_addlinkedsrvlogin 'JYOra817', 'FALSE', NULL, 'scott', 'tiger'
- JYOra817À̶ó´Â Linked server¿¡ scott/tiger¶ó´Â ¾ÆÀ̵ð¿Í Æнº¿öµå¸¦ ÀÌ¿ëÇؼ ·Î±×ÀÎÇÏ´Â Á¤º¸¸¦ µî·ÏÇÑ´Ù.
3. Linked server¸¦ ÀÌ¿ëÇÑ QUERY¼öÇàÇϱâ
(1) OPENQUERY() »ç¿ëÇϱâ
¾Õ¼ »ý¼ºÇÑ Linked Sever¸¦ ÀÌ¿ëÇؼ Query¸¦ ¼öÇàÇÏ°íÀÚ ÇÒ °æ¿ì OPENQUERY()¶ó´Â ÇÔ¼ö¸¦ ÀÌ¿ëÇÏ´Â ¹æ¹ýÀÌ ÀÖÀ¸¸ç ±¸¹®Àº ¾Æ·¡¿Í °°´Ù.
OPENQUERY ( linked_server , 'query' )
OPENQUERY¸¦ »ç¿ëÇÏ´Â ¿¹ÀÌ´Ù.
- select empno,ename,sal from OPENQUERY ( JYOra817 , 'select empno,ename,sal from scott.emp' )
- select * from OPENQUERY ( JYOra817 , 'select * from scott.emp')
where empno=7521
- insert OPENQUERY ( JYOra817 , 'select * from scott.emp where 1=0' )
values(100,'YONG','MANAGER',7902,'20041101',800,null,20)
- update OPENQUERY ( JYOra817 , 'select sal from scott.emp') set sal=100
- update JYOra817 ..SCOTT.EMP --- ´ë¹®ÀÚ·Î
SET sal = 100
- delete OPENQUERY ( JYOra817 , 'select rowid from scott.emp where empno=7566') | 18.97.9.172
|
|
|
Copyright 1999-2024 Zeroboard / skin by ÃÖ±æÈ£(gilho.kr@gmail.com)
|
|
|