Oracle資料庫使用DBLINK匯入遠程Oracle資料庫資訊

標籤：

    --1. 查詢DBLINK許可權    select * from sys.user_sys_privs t where t.privilege like upper(‘%link%‘);    --2. 賦予DBLINK許可權    grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to demobase;    --3. 查詢建立的DBLINK串連    select * from DBA_DB_LINKS;    --4. 建立DBLINK串連    create public database link to_db11 connect to demobase identified by demobase using ‘ (DESCRIPTION =       (ADDRESS_LIST =         (ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.11)(PORT = 1521))       )       (CONNECT_DATA =         (SERVER = DEDICATED)         (SERVICE_NAME = demo)       )     )‘;    --5. 刪除DBLINK串連    drop public database link to_db11;

 

    --查詢遠端資料庫的資訊    select * from demobase.my_demo@to_db11;    --複製表資訊    create table my_test as select * from my_demo where 1=2 ;    --全表複製    create table my_test as select * from demobase.my_demo@to_db11;    --建立暫存資料表，複製LOB欄位    create global temporary table my_temp as select * from demobase.my_demo@to_db11;

 

Oracle資料庫使用DBLINK匯入遠程Oracle資料庫資訊