Oracle 测试常用表BIG_TABLE-阿里云开发者社区

Oracle 测试常用表BIG_TABLE

2013-03-01 1156

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 创建测试用表，DBA经常用到，通常都是基于dba_objects来创建的比较多。本文根据Tom大师的big_table进行了整理，供大家参考。一、基于Oracle 10g下的big_table--=========================...

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