【ORACLE】Oracle测试常用表BIG_TABLE创建脚本

Oracle测试常用表BIG_TABLE创建脚本

DBA经常用到的创建测试用表，通常都是基于dba_objects来创建的比较多。
本文根据Tom大师的big_table进行了整理，供大家参考。

一、基于Oracle 10g下的big_table.sql

--============================================== 
-- Create a test table for Oracle 10g 
-- File : cr_big_tb_10g.sql 
-- Author : Robinson 
--============================================== 

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

--============================================== 
-- Create a test table for Oracle 11g 
-- File : cr_big_tb_11g.sql 
-- Author : Robinson 
--============================================== 

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、该脚本根据Tom大师的原代码big_table整理而成。
2、Oracle 11g all_objects 比Oracle 10g 多出两列，因此使用了2个不同的版本。
3、big_table的id列为唯一值，并在之上创建了primary key。
4、对于该表测试redo等相关信息是应启用logging模式。