Oracle 建立測試大表高效腳本：利用 DBA_OBJECTS 快速產生百萬級測試資料

效能測試、SQL 調優、分頁驗證常需大量測試資料。Oracle 字典視圖 DBA_OBJECTS (約 7-9 萬行) 是優質種子資料源。本文提供 PL/SQL 區塊，透過 INSERT /*+ APPEND */ 直接路徑載入 + 迴圈疊加，快速建立 500 萬行測試表，含完整欄位結構與提交控制。

一、建立目標表結構 (NOLOGGING 加速)

CREATE TABLE test NOLOGGING AS
SELECT ROWNUM AS id, a.*
FROM dba_objects a
WHERE 1=2;  -- 僅複製結構，不複製資料

-- 表結構包含：ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID,
-- DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME,
-- TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY

二、PL/SQL 高效載入腳本 (500 萬行)

DECLARE
  l_cnt  NUMBER;
  l_rows NUMBER := &1;  -- 目標行數，執行時傳入 5000000
BEGIN
  -- 首輪直接載入種子資料
  INSERT /*+ APPEND */ INTO test
  SELECT ROWNUM, a.*
  FROM dba_objects a;
  l_cnt := SQL%ROWCOUNT;  -- 約 7-9 萬
  COMMIT;

  -- 迴圈疊加至目標行數
  WHILE (l_cnt < l_rows) LOOP
    INSERT /*+ APPEND */ INTO test
    SELECT ROWNUM + l_cnt,
           owner, object_name, subobject_name,
           object_id, data_object_id,
           object_type, created, last_ddl_time,
           timestamp, status, temporary,
           generated, secondary
    FROM test  -- 關鍵：從自身讀取，避免重掃 DBA_OBJECTS
    WHERE ROWNUM <= l_rows - l_cnt;
    
    l_cnt := l_cnt + SQL%ROWCOUNT;
    COMMIT;
  END LOOP;
END;
/

-- 執行時輸入目標行數
Enter value for 1: 5000000

三、關鍵優化技巧解析

四、常見變體需求

• 指定表空間：CREATE TABLE test TABLESPACE users NOLOGGING AS ...
• 分區表測試：建立範圍分區表，載入腳本不變
• 自定義資料分布：修改 SELECT 列表，如 DBMS_RANDOM.VALUE、SYSDATE - DBMS_RANDOM.VALUE(0,365)
• 並行載入：INSERT /*+ APPEND PARALLEL(8) */ (需 Enterprise Edition)
• 統計資訊收集：載入完成後 EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST')

五、驗證腳本

SELECT COUNT(*) FROM test;                    -- 5000000
SELECT MIN(id), MAX(id) FROM test;            -- 1, 5000000
SELECT BYTES/1024/1024 MB FROM USER_SEGMENTS WHERE SEGMENT_NAME='TEST'; -- 表大小

此腳本在 10g/11g/12c/19c 均經實測，500 萬行通常 30-60 秒完成 (視存儲效能)。比 DBMS_RANDOM 逐行生成快數量級，是 DBA 與開發必備的測試資料製造利器。

1. 上一篇文章： 解決 ORA-12560: TNS:協議適配器錯誤：正確設定 ORACLE_SID 登入資料庫
2. 下一篇文章： Oracle SQL Developer 日期時間格式自訂：修正預設顯示異常、統一團隊開發標準

Oracle 資料庫相關知識

Oracle 閃回技術全景指南：閃回資料庫、閃回表、閃回查詢與版本查詢

Oracle 閃回技術僅處理「邏輯資料變更」，從根本改變恢復

Oracle 9i 全文檢索技術架構深度解析

Oracle Text 架構概覽 圖 1 Oracle Te

Oracle 核心機制深度剖析：Lock 與 Latch 的本質區別與性能影響

在 Oracle 資料庫並發控制體系中，Lock（鎖，內部稱

Oracle 正則表達式四大函數完整指南 (REGEXP_LIKE/SUBSTR/INSTR/REPLACE)

四大核心函數總覽 函數用途類比傳統函數 REGEXP_LI