实现目标:
1.现有两个数据库A: lwjc/lwjc@10.16.2.58:1521@orcl和B:mat/mat@10.16.1.6:1521/jtptdb
2.现将数据库A中表MAT_T_SNOW_REMOVAL_SNOWFALL的部分字段值插入到数据库B中表MAT_T_SNOW_REMOVAL_SNOWFALL中
实现思路:
1.sql developer连接jtptdb数据库
2.建立orcl数据数据库连接
3.检查连接成功后,利用sql中insert into 插入相关值
实现代码解析
1.连接进入A数据库,查看其global_names
2.连接B数据库建立orcl的数据库连接--查看global_names属性 select * from global_name
3.执行插入操作--连接orcl数据库 create database link orcl connect to lwjc identified by lwjc using --orcl是对应的数据库A的global_name '(DESCRIPTION =(ADDRESS_LIST=(ADDRESS = (PROTOCOL =TCP)(HOST=10.16.2.58)(PORT=1521))) (CONNECT_DATA =(SERVICE_NAME=orcl)))' drop database link orcl--若创建失败后可以删掉连接 --查询已经建立的远程连接名 select * from all_db_links --检查是否连接成功 select * from dual@orcl ----如果查到x则表明连接成功
4.完成查看是否插入成功--插入数值 insert into mat_t_snow_removal_snowfall t (t.snowfall_id,t.snow_info,t.del_flg,t.snowfall_code,t.snowfall_num,t.snow_group_id,t.station_bridge_id) select m.snowfall_id,m.snow_info,m.flg_del,m.snowfall_code,m.snowfall_num,m.group_code,rownum from mat_t_snow_removal_snowfall@orcl m
--查看表中信息 select * from MAT_T_SNOW_REMOVAL_SNOWFALL t --清空表中所有信息 delete from MAT_T_SNOW_REMOVAL_SNOWFALL