程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> 關於Oracle數據庫 >> Oracle 測試常用表BIG_TABLE

Oracle 測試常用表BIG_TABLE

編輯:關於Oracle數據庫

    創建測試用表,DBA經常用到,通常都是基於dba_objects來創建的比較多。本文根據Tom大師的big_table進行了整理,供大家參考。
    一、基於Oracle 10g下的big_table

    --==============================================
    -- Create a test table for Oracle 10g
    -- File   : cr_big_tb_10g.sql
    -- Author : Robinson
    -- Blog   : http://blog.csdn.net/robinson_0612
    --==============================================
    
    prompt
    prompt     Create a big table from all_objects
    prompt    ======================================
    CREATE TABLE big_table
    AS 
    SELECT ROWNUM id, a.*
    FROM all_objects a
    WHERE 1=0;
     
    prompt
    prompt  Modify table to nologgming mode
    prompt  ==========================
    ALTER TABLE big_table NOLOGGING;
     
    prompt 
    prompt     Please input rows number to fill into big_table
    prompt     ============================================
    DECLARE
            l_cnt NUMBER;
            l_rows NUMBER := &1;
    BEGIN
            INSERT /*+ append */
            INTO big_table
                    SELECT rownum, a.*
                    FROM all_objects a;
            l_cnt := SQL%ROWCOUNT;
            COMMIT;
            WHILE (l_cnt < l_rows)
            LOOP
                    INSERT /*+ APPEND */
                    INTO big_table
                            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 big_table
                            WHERE rownum <= l_rows - l_cnt;
                    l_cnt := l_cnt + SQL%ROWCOUNT;
                    COMMIT;
            END LOOP;
    END; 
    /
     
    prompt 
    prompt      Add primary key for  big table 
    prompt     =====================================
    ALTER TABLE big_table ADD CONSTRAINT 
    big_table_pk PRIMARY KEY (id);
     
    prompt 
    prompt      Gather statistics for big_table
    prompt     =====================================
    BEGIN
            dbms_stats.gather_table_stats(ownname => USER,
                                         tabname => 'BIG_TABLE',
                                         method_opt => 'for all indexed columns',
                                         cascade => TRUE);
    END; 
    /
     
    prompt 
    prompt      check total rows  for big_table 
    prompt     ====================================
    SELECT COUNT(*)
    FROM big_table;

    二、基於Oracle 11g下的big_table

    --==============================================
    -- Create a test table for Oracle 11g
    -- File   : cr_big_tb_11g.sql
    -- Author : Robinson
    -- Blog   : http://blog.csdn.net/robinson_0612
    --==============================================
    
    prompt
    prompt     Create a big table from all_objects
    prompt    ======================================
    CREATE TABLE big_table
    AS 
    SELECT ROWNUM id, a.*
    FROM all_objects a
    WHERE 1=0;
     
    prompt
    prompt  Modify table to nologgming mode
    prompt  ==========================
    ALTER TABLE big_table NOLOGGING;
     
    prompt 
    prompt      Please input rows number to fill into big_table
    prompt     ============================================
    DECLARE
            l_cnt NUMBER;
            l_rows NUMBER := &1;
    BEGIN
            INSERT /*+ append */
            INTO big_table
                    SELECT rownum, a.*
                    FROM all_objects a;
            l_cnt := SQL%ROWCOUNT;
            COMMIT;
            WHILE (l_cnt < l_rows)
            LOOP
                    INSERT /*+ APPEND */
                    INTO big_table
                            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
                                 ,namespace
                                 ,edition_name
                            FROM big_table 
                            WHERE rownum <= l_rows - l_cnt;
                    l_cnt := l_cnt + SQL%ROWCOUNT;
                    COMMIT;
            END LOOP;
    END; 
    /
     
    prompt 
    prompt      Add primary key for  big table 
    prompt     =====================================
    ALTER TABLE big_table ADD CONSTRAINT 
    big_table_pk PRIMARY KEY (id);
     
    prompt 
    prompt      Gather statistics for big_table
    prompt     =====================================
    BEGIN
            dbms_stats.gather_table_stats(ownname => USER,
                                         tabname => 'BIG_TABLE',
                                         method_opt => 'for all indexed columns',
                                         cascade => TRUE);
    END; 
    /
     
    prompt 
    prompt      check total rows  for big_table 
    prompt     ====================================
    SELECT COUNT(*)
    FROM big_table;
    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved