导入一张18亿条300G数据文件的表经验

最新推荐文章于 2024-01-08 15:50:53 发布
最新推荐文章于 2024-01-08 15:50:53 发布
阅读量334 收藏
点赞数
文章标签： 数据库 php
1.问题：需要导入一张18亿数据的300G大的数据文件

2. 客户给了一个用imp导出的数据文件。包含了一个表xx。这个表的结构如下：

点击(此处)折叠或打开

  1. CREATE TABLE "username"."xx"
  2.    (    "DTLCARDNO" CHAR(16) NOT NULL ENABLE,
  3.     "DTLCITY" NUMBER(4,0),
  4.     "DTLCDCNT" NUMBER(6,0) NOT NULL ENABLE,
  5.     "DTLTXNCODE" NUMBER(4,0) NOT NULL ENABLE,
  6.     "DTLINNTYPE" NUMBER(4,0),
  7.     "DTLPOSID" VARCHAR2(12),
  8.     "DTLSAMID" VARCHAR2(16),
  9.     "DTLPOSSEQ" NUMBER(10,0),
  10.     "DTLDATE" NUMBER(8,0),
  11.     "DTLTIME" NUMBER(6,0) NOT NULL ENABLE,
  12.     "DTLSETTDATE" NUMBER(8,0),
  13.     "DTLCENSEQ" NUMBER(10,0),
  14.     "DTLAMT" NUMBER(9,0) NOT NULL ENABLE,
  15.     "DTLSLAMT" NUMBER(9,0),
  16.     "DTLBEFBAL" NUMBER(9,0) NOT NULL ENABLE,
  17.     "DTLAFTBAL" NUMBER(9,0),
  18.     "DTLSTATID" NUMBER(9,0),
  19.     "DTLERRCODE" NUMBER(6,0),
  20.     "DTLINNERR" NUMBER(6,0),
  21.     "DTLRSVD" VARCHAR2(10),
  22.     "DTLPKGID" NUMBER(10,0),
  23.     "DTLUNITID" NUMBER(8,0),
  24.     "DTLCRDTYPE" NUMBER(4,0),
  25.     "DTLTAC" CHAR(8),
  26.     "PARTFLAG" NUMBER(3,0) NOT NULL ENABLE
  27.    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  28.   STORAGE(
  29.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  30.   TABLESPACE "CRDDTL01_TS"
  31.   PARTITION BY RANGE ("PARTFLAG")
  32.  (PARTITION "P_JY001" VALUES LESS THAN (1)
  33.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  34.   STORAGE(INITIAL 797966336 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  35.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  36.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  37.  PARTITION "P_JY002" VALUES LESS THAN (2)
  38.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  39.   STORAGE(INITIAL 751828992 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  40.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  41.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  42.  PARTITION "P_JY003" VALUES LESS THAN (3)
  43.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  44.   STORAGE(INITIAL 829423616 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  45.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  46.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  47.  PARTITION "P_JY004" VALUES LESS THAN (4)
  48.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  49.   STORAGE(INITIAL 886046720 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  50.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  51.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  52.  PARTITION "P_JY005" VALUES LESS THAN (5)
  53.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  54.   STORAGE(INITIAL 901775360 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  55.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  56.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  57.  PARTITION "P_JY006" VALUES LESS THAN (6)
  58.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  59.   STORAGE(INITIAL 826277888 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  60.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  61.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  62.  PARTITION "P_JY007" VALUES LESS THAN (7)
  63.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  64.   STORAGE(INITIAL 803209216 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  65.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  66.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  67.  PARTITION "P_JY008" VALUES LESS THAN (8)
  68.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  69.   STORAGE(INITIAL 961544192 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  70.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  71.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  72.  PARTITION "P_JY009" VALUES LESS THAN (9)
  73.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  74.   STORAGE(INITIAL 995098624 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  75.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  76.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  77.  PARTITION "P_JY010" VALUES LESS THAN (10)
  78.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  79.   STORAGE(INITIAL 972029952 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  80.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  81.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS , 
  82. 。。。。。。。。。。。。。。
  83. 类似的有360个分区


可以看到是个分区表。
导入语句

点击(此处)折叠或打开

  1. echo %time% ;
  2. imp system/manager@orcl file=E:\yikatong\tlcarddtltb.dmp fromuser=u1 touser=u1 ignore=y log=E:\yikatong\tlcarddtltb.dmp.log indexes=N RECORDLENGTH=65535 buffer=502400000 commit=n feedback=10000000
  3. echo %time% ;
要求1千万行响应一次，提交为N，不插入索引。buffer设置为500M

3.开始导入。因为客户也没有提供数据文件的大小，只提供了需要新建的表空间。我新建了如下可以自增的表空间。
create tablespace crddtl01_ts datafile 'D:/oracle/tablespace/crddtl01_ts.dbf' size 8024m autoextend on next 1024m autoallocate;

每次自增1024M。考虑到数据比较大，每次自增太小，花费时间小。接着开始导入了。等待了几个小时以后，报错：
01659, 00000, "unable to allocate MINEXTENTS beyond %s in tablespace %s"
// *Cause:  Failed to find sufficient contiguous space to allocate MINEXTENTS
//          for the segment being created.
// *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
//          tablespace or retry with smaller value for MINEXTENTS, NEXT or
//          PCTINCREASE
.刚开始有点蒙。我建立表空间都是自增长的。难道不能自增长。查询下可以增长的。看看表空间大小32G。原来oracle普通表空间文件最大为32G。然后开始给每个表空间增加4个文件。sql如下：

点击(此处)折叠或打开

  1. create tablespace crddtl01_ts datafile 'D:/oracle/tablespace/crddtl01_ts.dbf' size 8024m autoextend on next 1024m autoallocate;
  2. create tablespace crddtlidx01_ts datafile 'd:/oracle/tablespace/crddtlidx01_ts.dbf' size 5024m autoextend on next 1024m autoallocate;
  3. alter tablespace crddtl01_ts add datafile 'D:/oracle/tablespace/crddtl01a_ts.dbf' size 1024m autoextend on next 1024m ;
  4. alter tablespace crddtl01_ts add datafile 'D:/oracle/tablespace/crddtl01b_ts.dbf' size 1024m autoextend on next 1024m ;
  5. create tablespace crddtl02_ts datafile 'E:/oracle_data/crddtl02_ts.dbf' size 8024m autoextend on next 1024m autoallocate;

  7. alter tablespace crddtl02_ts add datafile 'E:/oracle_data/crddtl02a_ts.dbf' size 1024m autoextend on next 1024m ;
  8. alter tablespace crddtl02_ts add datafile 'E:/oracle_data/crddtl02b_ts.dbf' size 1024m autoextend on next 1024m ;

  10. create tablespace crddtlidx02_ts datafile 'E:/oracle_data/crddtlidx02_ts.dbf' size 5024m autoextend on next 1024m autoallocate;

  12. create tablespace crddtl03_ts datafile 'F:/oracleData/crddtl03_ts.dbf' size 8024m autoextend on next 1024m autoallocate;
  13. alter tablespace crddtl03_ts add datafile 'F:/oracleData/crddtl03a_ts.dbf' size 1024m autoextend on next 1024m;
  14. alter tablespace crddtl03_ts add datafile 'F:/oracleData/crddtl03b_ts.dbf' size 1024m autoextend on next 1024m;

  16. create tablespace crddtlidx03_ts datafile 'F:/oracleData/crddtlidx03_ts.dbf' size 5024m autoextend on next 1024m autoallocate;

  18. create tablespace crddtl04_ts datafile 'D:/oracle/tablespace/crddtl04_ts.dbf' size 8024m autoextend on next 1024m autoallocate;
  19. alter tablespace crddtl04_ts add datafile 'D:/oracle/tablespace/crddtl04a_ts.dbf' size 1024m autoextend on next 1024m ;
  20. alter tablespace crddtl04_ts add datafile 'D:/oracle/tablespace/crddtl04b_ts.dbf' size 1024m autoextend on next 1024m ;
于是接下来就开始了漫长的等待。
4.怎么知道导入了多少数据？导入进展到什么情况了呢？或者说导入进程有没有卡住，僵死呢？
第一看feedback.我在导入程序设置了参数feedback=10000000,每导入1千万数据，响应一个黑点。
第二可以看导入日志。每导入完一个分区，它会在日志插入一条记录。
第三打开资源管理器，我们看看imp进程，占用的cpu，硬盘，网络，内存资源。

但是这个服务器当时点什么都很慢。我分配一个8G的文件，花费了10几分钟。这个怎么解释呢？
5.经过漫长的等待，我花了2天7个小时，把这个18亿数据导入进去了。。
最后看看数据文件大小：

点击(此处)折叠或打开

  1. 目录                                   大小（M）tablespace
  2. D:\ORACLE\TABLESPACE\CRDDTL01A_TS.DBF  32767 CRDDTL01_TS
  3. D:\ORACLE\TABLESPACE\CRDDTL01_TS.DBF 32767 CRDDTL01_TS
  4. D:\ORACLE\TABLESPACE\CRDDTL01B_TS.DBF 32767 CRDDTL01_TS
  5. F:\ORACLEDATA\CRDDTL01D_TS.DBF 25600 CRDDTL01_TS
  6. E:\ORACLE_DATA\CRDDTL02C_TS.DBF 9216 CRDDTL02_TS
  7. E:\ORACLE_DATA\CRDDTL02D_TS.DBF 8192 CRDDTL02_TS
  8. E:\ORACLE_DATA\CRDDTL02B_TS.DBF 32767 CRDDTL02_TS
  9. E:\ORACLE_DATA\CRDDTL02_TS.DBF 32767 CRDDTL02_TS
  10. E:\ORACLE_DATA\CRDDTL02A_TS.DBF 30720 CRDDTL02_TS
  11. F:\ORACLEDATA\CRDDTL03D_TS.DBF 5120 CRDDTL03_TS
  12. F:\ORACLEDATA\CRDDTL03B_TS.DBF 28672 CRDDTL03_TS
  13. F:\ORACLEDATA\CRDDTL03_TS.DBF 32600 CRDDTL03_TS
  14. F:\ORACLEDATA\CRDDTL03A_TS.DBF 27648 CRDDTL03_TS
  15. F:\ORACLEDATA\CRDDTL03C_TS.DBF 5120 CRDDTL03_TS
  16. D:\ORACLE\TABLESPACE\CRDDTL04A_TS.DBF 28672 CRDDTL04_TS
  17. D:\ORACLE\TABLESPACE\CRDDTL04B_TS.DBF 32767 CRDDTL04_TS
  18. E:\ORACLE_DATA\CRDDTL04D_TS.DBF 7168 CRDDTL04_TS
  19. E:\ORACLE_DATA\CRDDTL04C_TS.DBF 7168 CRDDTL04_TS
  20. D:\ORACLE\TABLESPACE\CRDDTL04_TS.DBF 32767 CRDDTL04_TS
一个表空间有4个文件，几乎达到120G

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30393770/viewspace-2144252/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30393770/viewspace-2144252/

cll3830995
关注
数仓(七)：全量、 增量、缓慢变化维、拉链表
.
11-13 5496
一、解决增量导入由于数据修改导致数据重复问题 方案一：全量更新 目标表只保留最新的一份，比如我们每天用sqoop抽取最新的一份全量数据到hive 全量表 全量表没有分区，表中的数据是前一天的所有数据，比如说今天是24号，那么全量表里面拥有的数据是23号的所有数据，每次往全量表里面写数据都会覆盖之前的数据，所以全量表不能记录历史的数据情况，只有截止到当前最新的、全量的数据。 方式：每天drop掉前一天的数据，重新抽一份最新的。 优点：节省空间，一些普通的使用也很方便，不用在选择表的时候加一个时间分
Mysql学习总结（35）——Mysql两千万数据优化及迁移
科技D人生
09-07 3618
最近有一张2000W条记录的数据表需要优化和迁移。2000W数据对于MySQL来说很尴尬，因为合理的创建索引速度还是挺快的，再怎么优化速度也得不到多大提升。不过这些数据有大量的冗余字段和错误信息，极不方便做统计和分析。所以我需要创建一张新表，把旧表中的数据一条一条取出来优化后放回新表； 一. 清除冗余数据，优化字段结构 2000W数据中，能作为查询条件的字段我们是预知的。所以将这部分数据单
300万条mysql测试数据
08-31
300万条mysql测试数据，大量mysql数据，百万级mysql测试数据
oracle 踢重,Oracle在线重定义操作一张近300G的表的实验
weixin_36040444的博客
04-02 396
Oracle在线重定义操作一张近300G的表的实验author:zy目录：说明：本文相关表名、库名已过敏总结通过本实验，证明重定义是可以操作大表的。其原理也就是通过insert/*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */这种不会产生undo重定义需要的数据表空间、TEMP表空间、归档空间较多，在重定义之前需要准备充沛正确的重定...
mysql快速导入大数据量文件
最新发布
weixin_47766381的博客
01-08 530
​ 在导入大数据量mysql时会出现导入时间过长的问题,比如导入一个800M的mysql文件,普通导入需要2小时左右,但是在临时设置mysql的参数后,时间可以加快到2分钟左右,当时我只是设置了以下两个参数。操作参考: https://www.cnblogs.com/niway/p/17616722.html。
如何在十分钟内插入1亿条记录到Oracle数据库？
记事本
06-08 6427
如何在十分钟内插入1亿条记录到Oracle数据库？
cassandra 数据导出导入_cassandra百亿级数据库迁移实践
weixin_28872105的博客
12-24 705
迁移背景cassandra集群隔段时间出现rt飙高的问题，带来的影响就是请求cassandra短时间内出现大量超时，这个问题发生已经达到了平均两周一次的频率，已经影响到正常业务了。而出现这些问题的原因主要有以下3点：当初设计表的时候partition key设计的不是很合理，当数据量上去(最大的单表行数达到百亿级)之后，出现了一些数据量比较大的partition。单partition最多的数据量达...
Kettle--MySQL生产数据库千万、亿级数据量迁移方案及性能优化
m0_67401761的博客
07-29 3143
亿级以上数据量。
Blink SQL之创建数据结果表
胜利的曙光的博客
03-31 4142
Blink SQL至创建Sink结果表。
超大数据快速导入MySQL
高筑墙，广积粮，缓称王
11-27 1万+
一.问题背景由于论文实验需要,现有下面两个需求. 1. 将1G的csv文件数据导入MySQL单表sample中,记录数2000w+; 2. 将上面的sample表中的数据迁移到远程数据库中.二.解决方案对于需求1,有如下解决方式. 1. 使用Navicat 工具栏中’数据导入’功能; 2. 使用编写Python/Perl脚本导入(未实现). 3. 使用MySQL的LOAD DATA
oracle导入10个G的dmp,有哪位高手导入过30多G的dmp文件
weixin_35725410的博客
04-04 471
当前位置:我的异常网» Oracle管理»有哪位高手导入过30多G的dmp文件有哪位高手导入过30多G的dmp文件www.myexceptions.net网友分享于：2013-05-12浏览：148次有谁导入过30多G的dmp文件从生产数据库中导出的dmp文件是30多G，版本是10.2。现在要导入到测试数据库中，版本也是10.2，用的是imp进行导入imp xxxx/xxxx file...
Mysql 大量数据快速导入导出
热门推荐
11-11 7万+
【实验背景】项目中需要对数据库中一张表进行重新设计，
mysql线上数据库单表超过200G的处理
reblue520的专栏
08-09 2219
tbl_user_data占用了大量磁盘空间，数据表占用大概200G，索引30G左右，查询非常慢，影响业务的支持进行现在需要对它进行清理 临时解决方案是将原表重命名，新建一个和这个表相同的空表来替换(缺点是不能做到根治，隔一段时间以后需要重新处理) 根除的办法是重新设计，或者在客户端进行过滤避免过多垃圾数据进入系统 1.新建一个和现在表相同结构的表create table tbl_...
oracle的imp导入时覆盖目标数据库
weixin_34375054的博客
12-10 1695
背景：oracle正式库通过exp命令导出的dmp备份包，现在通过imp命令还原到测试库，测试库上面的表数据全部不要，要用新的。 方法：先删除用户、用户所在表空间，再新建用户和表空间，再imp导入。 过程： 1、用plsql查看用户的权限等信息并备份，例如 -- Create the user create user testuser;-- Grant/Revoke object priv...
oracle imp命令，将数据从一个用户导出的dmp数据，导入到另外一个用户下面。
oraclemch的专栏
04-26 9838
 ivr.dmp是用在生产库上通过用户shhvydivr导出的，要将数据导入到本地ivr用户里面。命令如下： imp file=D:/f/ivr.dmp fromuser=shhbydivr touser=ivr userid=ivr/ivr_2009@orcl
mysql source命令超大文件导入方法总结
han_cui的博客
09-07 6300
导入1G的数据，但是在怎么都导入不了，用命令行就可以轻松搞定了。用mysql source命令可以导入比较大的文件。  代码如下 复制代码 mysql>use dbtest; mysql>set names utf8; mysql>source D:/www/sql/back.sql; 通过source命令导入多个文件，可以新建一个so...
mysql一亿条数据如何排序存储到另外一张表
douxingpeng1的博客
03-25 898
1、分治 2、字典树 字典树是可以压缩数据量的一种数据结构
sql两张表的数据导入到一张表where条件怎么写
04-03
例如，假设有两张表A和B，要将它们的数据导入到一张表C中，同时只选择A表中age大于等于18的数据，可以使用以下SQL语句： ``` INSERT INTO C (name, age, gender) SELECT name, age, gender FROM A WHERE age >= 18 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值