[Oracle] create a large table for testing

Some big tables are sometimes used in my experiments. Here we will share how to use dba_objects to create and test large tables:

First, create a test table.

SYS @ ORCL> create table test nologging as select rownum id, a. * from dba_objects a where 1 = 2;

 

Table created.

Insert 5 million data records:

SYS @ ORCL> declare

Rochelle CNT number;

Rows number: = & 1;

Begin

Insert/* + append */into test select rownum, a. * from dba_objects;

L_cnt: = SQL % rowcount;

Commit;

While (l_cnt <l_rows)

Loop

Insert/* + append */into test 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

From sales

Where rownum <= l_rows-l_cnt;

L_cnt: = l_cnt + SQL % rowcount;

Commit;

End loop;

End; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 19 20 21

22/

Enter value for 1: 5000000

Old 3: l_rows number: = & 1;

New 3: l_rows number := 5000000;

 

PL/SQL procedure successfully completed.