Home > Databases, Oracle DB > create a big table with one million lines on oracle database for testing

create a big table with one million lines on oracle database for testing

May 10th, 2015

#First, create tablespace along with datafile

SQL> create tablespace test datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/test/datafile1.dbf' size 512m;

#Then create table. We disable logging to avoid unnecessary redo data

SQL> create table bigtab tablespace test as select rownum id, a.* from all_objects a where 1=0;
SQL> alter table bigtab nologging;

#now populate the table named bigtab

DECLARE
  L_CNT NUMBER;
  L_ROWS NUMBER := 1000000;
BEGIN
  INSERT /*+ APPEND */ INTO BIGTAB SELECT ROWNUM, A.* FROM ALL_OBJECTS A;
  L_CNT := SQL%ROWCOUNT;
  COMMIT;
  WHILE (L_CNT < L_ROWS)
  LOOP
    INSERT /*+ APPEND */ INTO BIGTAB
    SELECT ROWNUM+L_CNT,
      OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
      LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
    FROM BIGTAB
      WHERE ROWNUM <= L_ROWS-L_CNT;
    L_CNT := L_CNT + SQL%ROWCOUNT;
    COMMIT;
   END LOOP;
END;
/

#check result

SQL> select count(*) from bigtab;

COUNT(*)
----------
1000000

#get the tracefile of the session

SQL> SELECT TRACEFILE FROM V$SESSION S, V$PROCESS P WHERE S.PADDR=P.ADDR AND S.SID=SYS_CONTEXT('USERENV','SID');

SQL> alter session set events '10046 trace name context forever, level 12';

#to get the maximum number of blocks that can be read

[oracle@testvm ~]$ grep scattered <trace file from above>

WAIT #139828744903832: nam='db file scattered read' ela= 1715 file#=10 block#=14192 blocks=8 obj#=56403 tim=1431233617613776
WAIT #139828744903832: nam='db file scattered read' ela= 6836 file#=10 block#=14268 blocks=8 obj#=56403 tim=1431233617620994

PS: 

More info is here.

Good Luck!


Categories: Databases, Oracle DB Tags:
Comments are closed.