背景:
忘了
目标:
简单移库
实现:
一.相同用户空间下数据迁移(sql)
1.创T_BS_460表
CREATE TABLE "GPSSGP"."T_BS_460"
(
"ID" NUMBER,
"GPSSGP_ID" VARCHAR2(20 BYTE),
"MCC" NUMBER(3,0),
"MNC" NUMBER(3,0),
"AC" NUMBER(10,0),
"CI" NUMBER(12,0),
"VER" NUMBER(8,0),
"RADIUS" NUMBER(5,0),
"ADDRESS" VARCHAR2(255 BYTE),
"REF" NUMBER(10,0),
"ROADS" VARCHAR2(255 BYTE),
"LAT" NUMBER(11,8),
"LNG" NUMBER(11,8),
"LATA" NUMBER(11,8),
"LNGA" NUMBER(11,8),
"LATB" NUMBER(9,6),
"LNGB" NUMBER(9,6),
"LATG" NUMBER(9,6),
"LNGG" NUMBER(9,6),
"RID" NUMBER(6,0),
"RIDS" NUMBER(12,0),
PRIMARY KEY ("ID")
)
2.新建序列(为ID自增)
界面操作添加即可
3.尝试搜索出与新表字段对应的旧表数据
select SEQ_BS.nextval as ID, GPSSGP_ID,MCC,MNC,AC,CI,VER,RADIUS,ADDRESS,REF,ROADS,LAT,LNG,LATA,LNGA,LATB,LNGB,LATG,LNGG,RID,RIDS
from
(
select ID as GPSSGP_ID,MCC,MNC,AC,CI,VER,RADIUS,ADDRESS,REF,ROADS,LAT,LNG,LATA,LNGA,LATB,LNGB,LATG,LNGG,RID,RIDS
from BS_460
where ADDRESS like '%广西%'
) t1
4.查询并插入
select SEQ_BS.nextval as ID, GPSSGP_ID,MCC,MNC,AC,CI,VER,RADIUS,ADDRESS,REF,ROADS,LAT,LNG,LATA,LNGA,LATB,LNGB,LATG,LNGG,RID,RIDS
from
(
select ID as GPSSGP_ID,MCC,MNC,AC,CI,VER,RADIUS,ADDRESS,REF,ROADS,LAT,LNG,LATA,LNGA,LATB,LNGB,LATG,LNGG,RID,RIDS
from BS_460
where ADDRESS like '%广西%'
) t1
或者
Insert into T_BS_460 (ID,GPSSGP_ID,MCC,MNC,AC,CI,VER,RADIUS,ADDRESS,REF,ROADS,LAT,LNG,LATA,LNGA,LATB,LNGB,LATG,LNGG,RID,RIDS)
select SEQ_BS.nextval as ID, GPSSGP_ID,MCC,MNC,AC,CI,VER,RADIUS,ADDRESS,REF,ROADS,LAT,LNG,LATA,LNGA,LATB,LNGB,LATG,LNGG,RID,RIDS
from
(
select ID as GPSSGP_ID,MCC,MNC,AC,CI,VER,RADIUS,ADDRESS,REF,ROADS,LAT,LNG,LATA,LNGA,LATB,LNGB,LATG,LNGG,RID,RIDS
from BS_460
where ADDRESS like '%广西%'
) t1
二.不同用户空间的表、数据迁移(dmp)
1)用PC/SQL Developer 导出dmp文件,另一个用户空间导入dmp文件
要求:电脑装有oracle客户端
电脑装pc/SQL Developer
2)用cmd命令导出导入
exp gpssgp/gpssgp@192.168.10.235:1521/orcl file=D:\myfile\BS_460_T.dmp tables=T_BS_460
imp LOGMINER/LOGMINER@192.168.10.235:1521/orcl file=D:\myfile\BS_460_T.dmp full=y
要求:电脑装有oracle客户端、对客户端下配置文件(sqlnet.ora和tnsnames.ora)进行配置(D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN)
其中,sqlnet.ora : NAMES.DIRECTORY_PATH= (TNSNAMES,HOSTNAME)
注:tnsping cmstar可以查询G:\Oracle\product\10.1.0\Client_1\NETWORK\ADMIN目录下sqlnet.ora和tnsnames.ora文件配置是否可用
三.不同用户空间数据迁移(sql)
1.导出sql文件,用sqlDeveloper即可
2.cmd运行导入sql文件到目标数据库表(表要预先建好)
1.登陆目标数据库(以下代码有出入)
bsScan/bsScan@192.168.10.236:1521/orcl
sqlplus / as sysdba
2.导入数据
@C:\Users\Administrator\T_BS_460.sql