Monday, August 10, 2015

Generating Bulk of Data for Testing Purpose

Sometimes we need to generate a huge amount of data to perform some testing. Type of data to be generated depends on the scenario you want to test. I will show here a very simple example to create a very huge table. I usually use it if I need to have a huge tablespace or table to perform some test. For example, to test backup and recovery time of some tablespace with huge data, I would use this method to generate huge data

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release Production on Tue Jul 21 09:41:59 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create tablespace my_tbs datafile 'c:\my_tbs01.dbf' size 100m autoextend on next 100m;

Tablespace created.

10:27:33 SQL> create table test as select * from dba_objects;

Table created.

Elapsed: 00:00:00.89

10:27:55 SQL> alter table test move tablespace my_tbs;

Table altered.

Elapsed: 00:00:00.74

--Delete NULL from OBJECT_ID column if you want to test primary key index on this column.
10:28:08 SQL> delete from test where object_id is null;

1 row deleted.

Elapsed: 00:00:00.20
10:28:17 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
--Repeat INSERT statement to insert TEST table’s data into itself to increase size by 2X with every insert.

10:28:18 SQL> insert into test select * from test;

91122 rows created.

Elapsed: 00:00:00.17
10:28:22 SQL> insert into test select * from test;

182244 rows created.

Elapsed: 00:00:00.61
10:28:25 SQL> insert into test select * from test;

364488 rows created.

Elapsed: 00:00:03.35
10:28:29 SQL> insert into test select * from test;

728976 rows created.

Elapsed: 00:00:07.78
10:28:38 SQL> insert into test select * from test;

1457952 rows created.

Elapsed: 00:00:23.11
10:29:02 SQL> insert into test select * from test;

2915904 rows created.

Elapsed: 00:00:43.86
10:29:47 SQL> insert into test select * from test;

5831808 rows created.

Elapsed: 00:02:10.51
10:31:59 SQL> insert into test select * from test;

11663616 rows created.

Elapsed: 00:04:14.96
10:36:20 SQL> commit;

Commit complete.

Elapsed: 00:00:00.02
--Check size of table and tablespace after having more than 200 million rows in TEST table

10:36:20 SQL> select bytes/1024/1024 from dba_data_files where tablespace_name='MY_TBS';


Elapsed: 00:00:00.08
10:36:49 SQL> select bytes/1024/1024 from dba_segments where segment_name='TEST';


Elapsed: 00:00:00.33
10:37:10 SQL> select count(*) from test;


Elapsed: 00:00:30.59
10:37:52 SQL>

No comments: