I want to insert one billion records in the oracle database

Question

I have to insert one billion record in oracle database table. I have 2 column . One column is a seqence number which follows till 1,000,000,000 Second column is a varchar2 field ... and it is in the format of abc~122~373~7777777~5367 . That is first column must contain 3 random characters followed by '~' then 3 random number followed by '~' then again 3 random number followed by '~' then followed by 7 random number followed by '~' and finally 4 random number followed by '~'. And I need all records to be unique.

Answer 1

create or replace function fn  RETURN VARCHAR2 IS 
   label varchar2(24) := ''; 
BEGIN 
  -- abc~122~373~7777777~5367
  select 
    chr(97 + floor(dbms_random.value*26)) || chr(97 + floor(dbms_random.value*26)) || chr(97 + floor(dbms_random.value*26))|| '~' 
    || rpad(floor(dbms_random.value*123),3, '9') || '~' 
    || rpad(floor(dbms_random.value*123),3, '8') || '~' 
    || rpad(floor(dbms_random.value*1234567),7, '6') || '~' 
    || rpad(floor(dbms_random.value*1234),4, '4') into label 
  from dual ;

  RETURN label;
END;

and you could create a table by invoking it n times as below.

create table testtable as select fn from dual connect by level <= 1000000000;