오라클 19c 대량 샘플데이터 생성용 쿼리 벌크 인서트

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 대량 샘플데이터 생성용 쿼리 벌크인서트

벌크인서트를 이용해 대량 데이터를 만들 때 

회원번호, 전화번호, 주소, 금액, 일자, 일시 등의 샘플 데이터를 어떻게 만들면 좋을까 생각하다가 만들어본 쿼리

아래 벌크 인서트문의 데이터 형식 중 필요한 부분만 잘라내서 사용하면됨

 

 

테이블 생성

SQL>
drop table sample_t purge;
create table sample_t 
(id1 number, id2 number, name varchar2(10),  
date1 varchar2(8), date2 varchar2(8), date3 date, 
phone varchar2(13), price number, qty number, 
test1 number,  test2 varchar2(5), test3 varchar2(4)
)
nologging;

컬럼 설명

id1 : i 값을 넣는 컬럼

id2 : i 값과 랜덤값을 같이 넣어서 중복이 발생하지 않게 하는 컬럼

name : data 생성시 이름넣어야 할 때 사용하는 컬럼(랜덤이라 사람이름처럼은 나오지 않음)

date1 : 2010년부터 2021년까지 '20210303' 와 같은 랜덤 문자열 날짜를 넣어주는 컬럼

date2 : 2021년으로 고정하고 월일만 랜덤 문자열로 날짜를 넣어주는 컬럼

date3 : 2010년부터 2021년까지 '20210303 12:30:00' 과 같은 랜덤 date 타입 날짜일시를 넣어주는 컬럼

phone : 전화번호를 랜덤으로 넣어주는 컬럼

price : 상품 가격 등 가격을 랜덤으로 넣어주는 컬럼(1000원~10000원까지 1천원 단위로 생성)

qty : 상품 수량 등 수량을 랜덤으로 넣어주는 컬럼

test1 : 일반 숫자 컬럼

test2 : 일반 문자 컬럼

test3 : 일반 문자와 랜덤 숫자를 합친 컬럼

 

 

데이터 삽입(10만건)

SQL>
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..100000 LOOP 
   w_ins(i).id1   := i;
   w_ins(i).id2   := i||ceil(dbms_random.value(1, 10000000));
   w_ins(i).name  := dbms_random.string('x',5);
   w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
   w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
   w_ins(i).date3 := to_date(round(dbms_random.value(2010,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
   w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
   w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
   w_ins(i).qty   := ceil(dbms_random.value(1, 10));
   w_ins(i).test1 := 1234;
   w_ins(i).test2 := 'SQLP';
   w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..100000 INSERT INTO sample_t VALUES w_ins(i);
   COMMIT;
END;
/
 
PL/SQL procedure successfully completed.

 

 

데이터 조회

SQL> select * from sample_t where rownum <= 10;
 
       ID1      ID2 NAME     DATE1      DATE2    DATE3    PHONE           PRICE    QTY     TEST1 TEST2 TEST
---------- ---------- ---------- -------- -------- ------------ ------------- ---------- ---------- ---------- ----- ----
       354 3545727252 5VAX5     20191010 20210104 05-FEB-21    010-1672-1245        7000      7      1234 SQLP  A410
       355 3554538073 N78TP     20110615 20210604 07-OCT-19    010-7208-2070        3000      4      1234 SQLP  A912
       356 3564218134 N0901     20210318 20210622 20-MAY-21    010-3538-2899        5000      3      1234 SQLP  A920
       357 3579618053 1ONJ3     20130416 20210201 07-NOV-20    010-7276-9253        4000      4      1234 SQLP  A547
       358 3588664664 S8KSP     20110403 20210426 16-APR-19    010-5829-2943        7000     10      1234 SQLP  A745
       359  359330829 OIVPJ     20160626 20210220 14-AUG-19    010-3925-5858        6000      3      1234 SQLP  A385
       360   36038031 AHNC3     20141001 20210406 26-AUG-20    010-1471-4127        9000      5      1234 SQLP  A597
       361 3615691138 N7QLW     20100527 20211101 25-MAY-20    010-6096-7330        2000      5      1234 SQLP  A435
       362 3621206521 PUGQQ     20111202 20210927 03-JAN-19    010-3480-7358        2000      9      1234 SQLP  A558
       363 3632691153 MZGXU     20121005 20210708 26-FEB-20    010-1634-1662       10000      3      1234 SQLP  A151
 
10 rows selected.

 

 

date 조회 포맷 변경 후 데이터 조회

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
 
Session altered.
 
SQL> select * from sample_t where rownum <= 10;
 
       ID1      ID2 NAME     DATE1      DATE2    DATE3           PHONE          PRICE        QTY    TEST1 TEST2 TEST
---------- ---------- ---------- -------- -------- ------------------- ------------- ---------- ---------- ---------- ----- ----
       354 3545727252 5VAX5     20191010 20210104 2021-02-05 04:51:57 010-1672-1245       7000      7     1234 SQLP  A410
       355 3554538073 N78TP     20110615 20210604 2019-10-07 22:30:29 010-7208-2070       3000      4     1234 SQLP  A912
       356 3564218134 N0901     20210318 20210622 2021-05-20 23:01:37 010-3538-2899       5000      3     1234 SQLP  A920
       357 3579618053 1ONJ3     20130416 20210201 2020-11-07 07:48:52 010-7276-9253       4000      4     1234 SQLP  A547
       358 3588664664 S8KSP     20110403 20210426 2019-04-16 09:15:21 010-5829-2943       7000     10     1234 SQLP  A745
       359  359330829 OIVPJ     20160626 20210220 2019-08-14 08:40:23 010-3925-5858       6000      3     1234 SQLP  A385
       360   36038031 AHNC3     20141001 20210406 2020-08-26 03:21:08 010-1471-4127       9000      5     1234 SQLP  A597
       361 3615691138 N7QLW     20100527 20211101 2020-05-25 22:02:21 010-6096-7330       2000      5     1234 SQLP  A435
       362 3621206521 PUGQQ     20111202 20210927 2019-01-03 13:23:27 010-3480-7358       2000      9     1234 SQLP  A558
       363 3632691153 MZGXU     20121005 20210708 2020-02-26 11:41:51 010-1634-1662      10000      3     1234 SQLP  A151
 
10 rows selected.

이렇게 샘플 데이터를 간편하게 대량으로 만들 수 있음

 

 

*추가

대량 데이터 삽입시 undo 에러 발생시(1백만건 생성)

SQL>
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..10 LOOP
FOR i IN 1..100000 LOOP 
   w_ins(i).id1   := i;
   w_ins(i).id2   := i||ceil(dbms_random.value(1, 10000000));
   w_ins(i).name  := dbms_random.string('x',5);
   w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
   w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
   w_ins(i).date3 := to_date(round(dbms_random.value(2019,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
   w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
   w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
   w_ins(i).qty   := ceil(dbms_random.value(1, 10));
   w_ins(i).test1 := 1234;
   w_ins(i).test2 := 'SQLP';
   w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..100000 INSERT INTO sample_t VALUES w_ins(i);
   COMMIT;
END LOOP;
END;
/

for문으로 한번 더 감싸서 벌크인서트가 10번 반복 실행되게끔 하면됨

 

 

참조 : https://positivemh.tistory.com/242

오라클 샘플 데이터 만들기, 테스트 데이터 벌크인서트 + dbms_random

https://positivemh.tistory.com/183

대량 데이터 삽입 insert 빠르게 실행 벌크 insert