oracle如何快速创建千万数量级数据

最近项目需要对hadoop-cdh进行性能测试，由于是大数据处理方面，造数据就显得很重要了。

造数据前，想到了两种造数据方法。一种是用之前测试时写的造数据脚本造数据。另一种是在数据库现有的基础上造数据。前一种方法是通过python脚本写的，主要是为测试方便写的，时间方面肯定会大大加长。另外很重要的原因，脚本并没有对所有表写了造数据的方法。
刚好有券商提供的测试数据，可以作为基础数据。在基础数据上，再大批量造数据，效率会大大提升，同时数据广度也会更好。

确定造数据方法

假设在券商提供的测试数据中，用户账户表，用户数量是200个；有这么一张表，his_deliver历史交割表，有60000条数据。现需要200万用户量，那么，用户账户表需要扩展到200万，his_deliver表需要扩展到6亿数据。

1、 根据业务，确定这张表哪些字段需要特殊处理。比如用户账户表的账户字段fund_account。

2、 选择合适的方法处理字段。比如扩展用户账户表，由于需要从200到2000000，需对表内每条数据进行10000倍扩展，可以在每个fund_account字段值后拼接00001~10000。
下面以his_deliver历史交割表数据为例。
根据上述方法第1步，fund_account(资金账户)、client_id(用户id)、SERIAL_NO(交易流水号)、position_str(定位串)四个字段需要特殊处理。（一个用户有一个资金账户与一个id,每条交易记录，）
根据上述方法第2步，决定fund_account字段值后拼接00001~10000，client_id字段值后拼接00001~10000，SERIAL_NO字段值后拼接00001~10000，position_str用init_date(交易日期) 与 SERIAL_NO字段拼接。

3、将在his_deliver_a表处理过的数据，插到his_deliver_b中。
想法有了,如何具体操作。一开始想用python写个脚本，后来一想，代码写起来并不复杂，故选用sql写。

实现思路

1、 创建与his_deliver表结构相同的两张表
his_deliver_a, his_deliver_b。his_deliver是源数据表，万一写的sql有问题，不至于源数据被修改。his_deliver_a表内初始数据是与源数据一致的。主要处理相关字段，使之符合我们的要求。His_deliver_b是存储经his_deliver_a处理过的数据，也是最终数据。
实现方法：

create table hs_his.his_deliver_a as select * from hs_his.his_deliver;

上述sql，创建一张his_deliver_a表，并将his_deliver的数据插入his_deliver_a内。简单来说就是复制his_deliver表，命名为his_deliver_a。

create table hs_his.his_deliver_b as select * from hs_his.his_deliver where 1 = 2;

上述sql，创建一张his_deliver_b表，his_deliver_b表与his_deliver表结构相同，数据为空。

2、 根据实现方法，写一个update语句。
实现方法：

  update hs_his.his_deliver_a hd
       set hd.fund_account = substr(hd.fund_account,1,9)||lpad(to_char(i),5,'0'), 
       hd.client_id = substr(hd.client_id,1,9)||lpad(to_char(i),5,'0'),
       hd.SERIAL_NO = substr(hd.SERIAL_NO,1,9)||lpad(to_char(i),5,'0'),
       hd.position_str = CONCAT(hd.init_date, hd.SERIAL_NO);

券商提供的脱敏fund_account都是9位数字。substr(hd.fund_account,1,9)表示从fund_account取长度为9。若fund_account=1234567890,那么substr(hd.fund_account,1,9)结果为：123456789。lpad(to_char(i),5,’0’)中的“i”是一个正整数，整个语句表示将“i”由整形转化为字符型，而后从to_char(i)中前5个字符，若位数不足，前面5个补0。若i=123,那么lpad(to_char(i),5,’0’) 结果为：00123。“||”与concat()函数都表示拼接字符串。若init_date=20170909, SERIAL_NO =123456,那么CONCAT(hd.init_date, hd.SERIAL_NO)结果为：20170909123456。

3、 将his_deliver_a数据插入his_deliver_b表

    insert into hs_his.his_deliver_b  select * from hs_his.his_deliver_a;

4、 写一个循环体。
结合上述2~3步骤，完整结果如下：

declare
  i Integer;
begin
  i := 1;
  while i <= 10000 loop
    update hs_his.his_deliver_a hd
       set hd.fund_account = substr(hd.fund_account,1,9)||lpad(to_char(i),5,'0'), 
       hd.client_id = substr(hd.client_id,1,9)||lpad(to_char(i),5,'0'),
       hd.SERIAL_NO = substr(hd.SERIAL_NO,1,9)||lpad(to_char(i),5,'0'),
       hd.position_str = CONCAT(hd.init_date, hd.SERIAL_NO);
    insert into hs_his.his_deliver_b
      select * from hs_his.his_deliver_a;
    i := i + 1;
    commit;
  end loop;
  commit;
end;
/

拷贝上述代码在服务器上跑，表数据从60000–>600000000数据量略大，能跑上个把小时。

表空间不足

看着很顺利，但是人生处处有拦路虎。跑了一段时间，发现程序报错了，错误如下：

ERROR at line 1:
ORA-01653: unable to extend table HS_HIS.HIS_DELIVER_B by 8192 in tablespace
HS_HIS_DATA
ORA-06512: at line 11

上述报错大致意思是，表空间大小不够。如何解决？

1、确认问题
可以通过以下语句查看表空间使用情况：

SELECT a.tablespace_name, 
a.bytes total, 
b.bytes used, 
c.bytes free, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name;

如上图，着重关注一下表his_deliver_b所在的表空间HS_HIS_DATA的使用情况。查看FREE栏里的数据，数据单位为字节，除以1024^3可转换成G。
若不知表所对应的表空间怎么办？
在PL/SQL客户端，右键点击表名，选择view–>View SQL，如下图,得到表空间名为HS_HIS_DATA。

2、如何增加表空间
发现表空间确实不够，如何增加。一个表空间由多个数据文件组成：1、调整每个数据文件大小容量上限。2、增加数据文件，以此来达到增加表空间。此处我们采用第2种方法。
1) 查询得到数据文件的储存路径

select *  from dba_data_files where tablespace_name = 'HS_HIS_DATA';

如下图，字段FILE_NAME为数据文件存储路径。

2) 执行增加数据文件命令

alter tablespace HS_HIS_DATA add datafile '/oracle/ora10g/oradata/uf20/hisdat17.dbf' size 30000m;

/oracle/ora10g/oradata/uf20/hisdat17.dbf是一个新文件，若原来存在此文件，文件名换一个未命名的。30000m表示增加表空间30000兆大小（约30G）。
注：每个数据文件的大小是有上限的，默认约40G。

3、问题解决数据如何处理
若按照本文方法造数据，发现此问题后，不需重新跑数据（时间成本太大），重新设置i初始值可继续跑数据。
1) 更改变量i初始值
查询his_deliver表数据量为count_1，查询his_deliver_b表数据量为count_2。
设置i初始值为 count_2/count_1 + 1即可。
若count_2/count_1 + 1 = 4002，则

declare
  i Integer;
begin
  i := 4002;

数据造完毕后，若无异常，先重命名his_deliver为his_deliver_source,再重命名his_deliver_b为his_deliver。

rename table his_deliver to his_deliver_source;
rename table his_deliver_b to his_deliver;