오라클 대량 더미 데이터 넣기

/** 데이터 복제 **/
/*계층형 쿼리를 사용 하여 인위적으로 데이터 생성*/
select * from dual connect by level <= 1000;

/*카티션 곱 조인과 계층형 쿼리의 혼용*/
select * from A, (select level from dual connect by level <= 2)
;

/*랜덤 숫자*/
select trunc(dbms_random.value(1, 100)) from dual;

/*랜덤 대문자*/
select dbms_random.string('U',10) from dual;

/*랜덤 소문자*/
select dbms_random.string('L',10) from dual;

/*
    테이블 만들고 더미 데이터 넣기
*/

drop table TB_CUST purge;

CREATE TABLE TB_CUST(
    CUST_ID VARCHAR2(10), /*고객id*/
    CUST_NM VARCHAR2(50), /*고객명*/
    BIRTH_DT VARCHAR2(8), /*생일*/
    INST_DT VARCHAR(8), /*입력일자*/
    INST_ID VARCHAR2(10), /*입력자id*/
    INST_NM VARCHAR2(50) /*입력자명*/
);

DROP TABLE TB_ORD;

CREATE TABLE TB_ORD(
    ORD_NO VARCHAR2(15), /*주문번호*/
    ORD_DT VARCHAR2(8), /*주문일자*/
    PRDT_CD VARCHAR2(6), /*제품코드*/
    ORD_AMT NUMBER(15), /*주문금액*/
    DIS_AMT NUMBER(15), /*할인금액*/
    INST_DT VARCHAR2(8), /*입력일자*/
    INST_ID VARCHAR2(10), /*입력자id*/
    INST_NM VARCHAR2(50), /*입력자명*/
    CUST_ID VARCHAR2(10) /*고객id*/
);

/*고객테이블 100만건 인설트*/
INSERT INTO TB_CUST /*고객테이블*/
SELECT LPAD(TO_CHAR(ROWNUM), 10, '0') /*고객ID*/
     , DBMS_RANDOM.STRING('U', 10) /*고객명*/
     , TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(365,36500)), 'YYYYMMDD') /*생일*/
     , TO_CHAR(SYSDATE, 'YYYYMMDD') /*입력일자*/
     , 'chunkind'
     , 'system'
 from dual connect by level <= 1000000
;

commit;

/*
    주문 테이블 1000만건 인설트
    너무 많으니 NOLOGGING 모드 설정하자
*/

ALTER TABLE TB_ORD NOLOGGING;

INSERT /*+ APPEND */ INTO TB_ORD 
SELECT LPAD(TO_CHAR(ROWNUM), 15, '0') /*주문번호*/
     , TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(365, 3650)), 'YYYYMMDD') /*주문일자*/
     , DBMS_RANDOM.STRING('X', 6) /*제품코드*/
     , TRUNC(DBMS_RANDOM.VALUE(1000, 100000)) /*주문금액*/
     , TRUNC(DBMS_RANDOM.VALUE(100, 10000)) /*할인금액*/
     , TO_CHAR(SYSDATE, 'YYYYMMDD') /*입력일자*/
     , 'chunkind' /*입력자id*/
     , 'system' /*입력자명*/
     , A.CUST_ID /*고객id*/
  FROM TB_CUST A, (select level from dual connect by level <= 10);

commit;