Oracleデータベースで大量データを生成する簡単な方法は？メモリー不足にならない方法も解説！

Oracleデータベースで大量のテストデータを生成する方法を説明します。大量のテストデータが必要、何億件もの大量データを生成しなければならないという場合に使えるテクニックを紹介します。

よくある方法はPL/SQLでデータを生成する方法が紹介されていますが、本記事では1つのSQLだけで何億件ものテストデータを簡単に生成する方法を紹介しています。

また、あまりにも大量のテストデータを生成しようとした場合、メモリー不足エラー(ORA-04030)になることがあります。サーバー環境によりますが、例えば100億件のデータを生成しようとするとメモリー不足エラー(ORA-04030)が発生することがあります。その回避方法も併せて紹介します。

大量データをSQL一発で生成する
大量データ生成時にメモリー不足エラー発生 (ORA-04030)
メモリー不足にならない大量テストデータ生成方法
まとめ

大量データをSQL一発で生成する

大量のテストデータを生成する前に、まずテスト用として以下のようなテーブルを作成します。

CREATE TABLE sample_table (
    user_id       NUMBER,
    user_name     VARCHAR2(50),
    created_date  DATE
);

このテーブルに対して、まず10件程度のデータをINSERTしてみます。テスト用のデータをINSERTする場合、よくPL/SQL関数を作成してINSERTする方法が紹介されていますが、INSERT文とSELECT文だけで大量のテストデータを生成することが出来ます。以下のように「CONNECT BY」句を使うことによって簡単に任意の数のレコードをINSERTすることが出来ます。「CONNECT BY」句は「階層問い合わせ」と呼ばれる方法です。

INSERT INTO sample_table (user_id, user_name, created_date)
SELECT
  level, 'user' || level, SYSDATE
FROM DUAL
CONNECT BY level <= 10;

上記SQLを実行すると、以下のように10件のデータがINSERTされていることが分かります。

SQL> SELECT * FROM sample_table;

   USER_ID USER_NAME    CREATED_DATE
__________ ____________ _______________
         1 user1        06-JAN-25
         2 user2        06-JAN-25
         3 user3        06-JAN-25
         4 user4        06-JAN-25
         5 user5        06-JAN-25
         6 user6        06-JAN-25
         7 user7        06-JAN-25
         8 user8        06-JAN-25
         9 user9        06-JAN-25
        10 user10       06-JAN-25

10 rows selected.

100万件のテストデータを生成する場合には、次のように「CONNECT BY level <= 1000000」とすることで、生成するデータ件数を簡単に変更することが可能です。

INSERT INTO sample_table (user_id, user_name, created_date)
SELECT
  level, 'user' || level, SYSDATE
FROM DUAL
CONNECT BY level <= 1000000;

大量データ生成時にメモリー不足エラー発生 (ORA-04030)

あまりにも大量のデータを生成しようとすると、次のように「ORA-04030: out of process memory」というエラーが出てしまう場合があります。Oracleデータベースサーバーのメモリー割り当て設定にもよりますが、例えば私の環境では100億件のテストデータを生成しようとするとメモリーエラーとなります。

ORA-04030 - Database Error Messages

More Details :

ORA-04030 - Database Error Messages

" style="color:#d8dee9ff;display:none" aria-label="Copy" class="code-block-pro-copy-button">

SQL> INSERT INTO sample_table (user_id, user_name, created_date)
  2  SELECT
  3    level, 'user' || level, SYSDATE
  4  FROM DUAL
  5* CONNECT BY level <= 10000000000;

次のコマンドの開始中にエラーが発生しました : 行 1 -
INSERT INTO sample_table (user_id, user_name, created_date)
SELECT
  level, 'user' || level, SYSDATE
FROM DUAL
CONNECT BY level <= 10000000000
エラー・レポート -
ORA-04030: out of process memory when trying to allocate 80 bytes (kxs-heap-w,cursor work heap)

https://docs.oracle.com/error-help/db/ora-04030/

More Details :
https://docs.oracle.com/error-help/db/ora-04030/

これは大量のテストデータをメモリー上で生成しようとしてメモリー不足エラーとなってしまうことが原因です。このエラーを回避するための方法を次に紹介します。

メモリー不足にならない大量テストデータ生成方法

先ほど見たようにメモリーエラーを発生させずに何億件もの大量テストデータを生成したい場合、次のような方法で大量テストデータを生成することが出来ます。

メモリー不足エラーを回避するには、WITH句を使う方法で、さらにWITH句の中で「/*+ materialize */」というヒント句を使う部分です。この方法によって「グローバル一時テーブル」という一時的なテーブルを使ってデータを生成する為、何億ものテストデータを生成してもメモリーエラーにならずにデータを生成してINSERTすることが出来ます。

WITH data AS (
  SELECT /*+ materialize */ level AS id
  FROM DUAL
  CONNECT BY level <= 10000
)
SELECT rownum AS id, 'user' || rownum, SYSDATE
FROM data, data, data
WHERE rownum <= 10000000000;

下記の例では100億件のテストデータを生成してsample_tableテーブルにデータをINSERTしています。生成するデータ数は「WHERE rownum <= 10000000000」の数値部分を変更することで任意の数のレコードをを生成することが出来ます。

INSERT INTO sample_table (user_id, user_name, created_date)
WITH data AS (
  SELECT /*+ materialize */ level AS id
  FROM DUAL
  CONNECT BY level <= 10000
)
SELECT rownum AS id, 'user' || rownum, SYSDATE
FROM data, data, data
WHERE rownum <= 10000000000;