Generate Dummy Data

[samc]

Dear all,
how can i generate 20gb of dummy data in my database? please advise
tq

[abhaysk]

Use loops and insert constants or value returned from function (random generated)

Abhay.

[DaPi]

You can adapt Tamil's method to create a vast number of rows:

Originally posted by tamilselvan

select rownum as run_num,
       to_date('15/01/2004','dd/mm/yyyy')+ rownum from 
       (select 1  from dual group by cube (1,1,1,1,1)) ; 

If there are n 1's in the cube, you get 2**n rows.

[padders]

Have you actually tried that? Be advised that it might suck depending on your version. A cube with 12 dimensions returns a mere 4096 rows (discussion is regarding millions). Compare times for 8i, 9.2 and 10g below...

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production

SQL> SET TIMING ON;
SQL> SELECT COUNT (*)
  2  FROM  (SELECT 1
  3         FROM   dual
  4         GROUP BY CUBE (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1));

  COUNT(*)
----------
      4096

Elapsed: 00:00:00.00
SQL> 

Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> SET TIMING ON;
SQL> SELECT COUNT (*)
  2  FROM  (SELECT 1
  3         FROM   dual
  4         GROUP BY CUBE (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1));

  COUNT(*)
----------
      4096

Elapsed: 00:00:52.09
SQL> 

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SET TIMING ON;
SQL> SELECT COUNT (*)
  2  FROM  (SELECT 1
  3         FROM   dual
  4         GROUP BY CUBE (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1));

  COUNT(*)
----------
      4096

Elapsed: 00:01:34.07
SQL>

[DaPi]

I knew there was at least one good reason to stick with 8i ! ! ! !


Just tried with a 24-cube to give 16Meg rows - blows my temp storage! So it looks like a more modest cube in a PL/SQL loop is more practical.

[tamilselvan]

To use CUBE, you need very high speed CPU and a lot of TEMP tablespace.

You can try this method:

execute dbms_random.seed(630522626) ;

create table random_table
tablespace large_data
as
select
substr(dbms_random.string('a',30),1,30) id1,
substr(dbms_random.string('a',30),1,30) id2,
substr(dbms_random.string('a',30),1,30) id3,
substr(dbms_random.string('a',30),1,30) id4,
substr(dbms_random.string('a',30),1,30) id5,
substr(dbms_random.string('a',30),1,30) id6,
substr(dbms_random.string('a',30),1,30) id7,
substr(dbms_random.string('a',30),1,30) id8,
substr(dbms_random.string('a',30),1,30) id9,
substr(dbms_random.string('a',30),1,30) id10
from dba_objects where rownum < 32000
;

Once the table is created, you can use insert into the random_table by selecting rows from the same table. Or you can use dbms_random.string within the select in the insert statement.

Tamil

[samc]

Thanks will try out with urs recommendation.