Local database SID=RACE7
Remote Database SID=DEV10
if network is being setup ready.
1) EDIT tnsname.ora
DEV10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = lg)(PORT = 1523)) )
(CONNECT_DATA = (SERVICE_NAME = dev10)
)
)
2) Create a public(or private) db link object
SQL> CREATE PUBLIC DATABASE LINK MY_TP CONNECT TO IFSAPP IDENTIFIED BY IFSAPP USING 'dev10';
Database link created.
3) Copy table from remote database to local database
SQL> COPY FROM IFSAPP/IFSAPP@DEV10 CREATE LONGINE_TAB
USING SELECT * FROM abc_class_TAB;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)Table ABC created.
3 rows selected from IFSAPP@DEV10.
3 rows inserted into LONGINE_TAB.
3 rows committed into LONGINE_TAB at DEFAULT HOST connection.
4)SELECT * FROM LONGINE_TAB;
ABC_CLASS ABC_PERCENT ROWVERSION
------------------ ------------------------- -------------------------
A 80 2007/06/28
B 15 2007/06/28
C 5 2007/06/28
5)delete from LONGINE_TAB;
6)select * from abc_class@my_tp;
ABC_CLASS ABC_PERCENT ROWVERSION
------------------ ------------------------- --------------------------------
A 80 20070628151609
B 15 20070628151609
C 5 20070628151609
7)COPY FROM IFSAPP/IFSAPP@DEV10 INSERT LONGINE_TAB USING SELECT * FROM abc_class_TAB;
Array fetch/bind size is 15.
(arraysize is 15)Will commit when done.
(copycommit is 0)Maximum long size is 80. (long is 80)
3 rows selected from IFSAPP@DEV10.
3 rows inserted into LONGINE_TAB.
3 rows committed into LONGINE_TAB at DEFAULT HOST connection.
8)select owner,object_name from dba_objects where object_type='DATABASE LINK';
( or select owner, db_link from dba_db_links; )
ower db_linkpublic
public my_tp.WORLD
9)drop public database link my_tp;
( or drop public database link my_tp.WORLD;)
