Generate Dummy Data

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

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

Abhay.

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

Quote:

---

Originally posted by tamilselvan

PHP Code:

---

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.

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...

Code:

---

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>

---

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.

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

Thanks will try out with urs recommendation.