Oracle Databaseで大きなダミー表を作る

検証用に大きなダミー表が必要な場合の作成メモ。

CREATE TABLE smalltable AS
  SELECT rownum AS num, dbms_random.string('A', 100) AS str
  FROM (SELECT 0 FROM dba_objects WHERE rownum <= 1000);

SELECT TO_CHAR(bytes / 1024 / 1024, '9,999,990.900') AS "MB"
FROM user_segments
WHERE segment_name = 'SMALLTABLE';

CREATE TABLE bigtable AS
  SELECT
    t1.num * 1000000 + t2.num * 1000 + t3.num AS num,
    t1.str AS str1,
    t2.str AS str2,
    t3.str AS str3
  FROM smalltable t1, smalltable t2, smalltable t3;

SELECT TO_CHAR(bytes / 1024 / 1024, '9,999,990.900') AS "MB"
FROM user_segments
WHERE segment_name = 'BIGTABLE';