DBLINK 复制表数据

两台不同sid的服务器间复制数据 DBLINK。A数据库中需要复制B的表数据。

1、查看A数据库用户是否有DBLINK 的权限

select * from user_sys_privs where privilege like upper('%DATABASE LINK%'); 

2、wu则需要使用 sysdba 角色给用户赋权：

grant create public database link,create database link to scott;

3、创建DBLINK

create public database link alat_dblink connect totest_USER identified by test_USER
　　 using '(DESCRIPTION =
                                                 (ADDRESS_LIST =
                                                                                  (ADDRESS = (PROTOCOL = TCP)(HOST =10.1.2.9)(PORT =1521))
                                                                                 )
                                                (CONNECT_DATA =
                                                                                  (SERVICE_NAME =orcl)
                                                                                    )
                        )';       --紫色部分未B数据库连接信息

4、检查

select * from APP_LOGIN@alat_dblink;    --app_login  B数据库的表名

5、建表 复制数据

create table appuser as select * from APP_LOGIN@alat_dblink;

6、删除DBLINK

drop public datebase  link alat_dblink