oracle创造亿级别数据过程

关注

原创最新推荐文章于 2026-02-18 16:10:27 发布 871 阅读 AI 写同款· GEO 优化 ›

Oracle中如何对超大规模数据（如超过2亿条）直接用SQL语句入库？在实际生产环境下，有时需将超大规模数据（如超过2亿条）导入分区表，但因原表与目的分区表的字段不一致，且用Kettle等转换工具极易失败，而用insert into select t1,t2.. from tab的Oracle SQL方式直接执行将导致redo、undo日志暴增，使数据库容易假死。针对此问题，采用文中方法可完美解决，且对系统性能几乎无影响（不管是分区表还是普通堆表均可）。立即下载

create table EMP
(
EMPNO NUMBER(8) PRIMARY KEY,
ENAME VARCHAR2(20),
MGR NUMBER(20),
HIREDATE DATE
);

create table che
(
id NUMBER(8) PRIMARY KEY,
name VARCHAR2(20),
HIREDATE DATE
);

declare
i integer;
begin
i := 1;
loop
insert into che (id,name,HIREDATE)
values
(i,
'苏F 00001',
SYSDATE);
i := i + 1;
exit when i > 200000;
end loop;
commit;
end;

create table datatemp
(
gmsfhm NUMBER(18) PRIMARY KEY,
name VARCHAR2(20),
sex VARCHAR2(20),
birthday VARCHAR2(20),
lxdh VARCHAR2(20)
);

declare
i integer;
begin
i := 1;
loop
insert into datatemp (gmsfhm,name,sex,birthday,lxdh)
values
(320602+i,
'小拾',
'男',
'2019-07-25',
'14712345678');
i := i + 1;
exit when i > 500000;
end loop;
commit;
end;

create table house
(
gmsfhm NUMBER(8) PRIMARY KEY,
name VARCHAR2(20),
FWMJ VARCHAR2(20),
address VARCHAR2(50),
lxdh VARCHAR2(20)
);

declare
i integer;
begin
i := 1;
loop
insert into house (gmsfhm,name,FWMJ,address,lxdh)
values
(320602+i,
'小拾',
'98',
'北京市x区x小区',
'14712345678');
i := i + 1;
exit when i > 100000;
end loop;
commit;
end;

create table che
(
gmsfhm NUMBER(18) PRIMARY KEY,
name VARCHAR2(20),
chepai VARCHAR2(20),
color VARCHAR2(20),
lxdh VARCHAR2(20)
);

declare
i integer;
begin
i := 1;
loop
insert into che (gmsfhm,name,chepai,color,lxdh)
values
(320602+i,
'小拾',
'苏F0555A',
'黑色',
'14712345678');
i := i + 1;
exit when i > 500000;
end loop;
commit;
end;