Common Oracle test table BIG_TABLE

Source: Internet
Author: User
Tags rowcount

Create test tables, which are frequently used by DBAs. Generally, they are created based on dba_objects. This article is based on the big_table of Master Tom for your reference.
1. big_table based on Oracle 10 GB

--==============================================-- Create a test table for Oracle 10g-- File   : cr_big_tb_10g.sql-- Author : Robinson-- Blog   : http://blog.csdn.net/robinson_0612--==============================================promptprompt     Create a big table from all_objectsprompt    ======================================CREATE TABLE big_tableAS SELECT ROWNUM id, a.*FROM all_objects aWHERE 1=0; promptprompt  Modify table to nologgming modeprompt  ==========================ALTER TABLE big_table NOLOGGING; prompt prompt     Please input rows number to fill into big_tableprompt     ============================================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_tableprompt     =====================================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;

Ii. big_table based on Oracle 11g

--==============================================-- Create a test table for Oracle 11g-- File   : cr_big_tb_11g.sql-- Author : Robinson-- Blog   : http://blog.csdn.net/robinson_0612--==============================================promptprompt     Create a big table from all_objectsprompt    ======================================CREATE TABLE big_tableAS SELECT ROWNUM id, a.*FROM all_objects aWHERE 1=0; promptprompt  Modify table to nologgming modeprompt  ==========================ALTER TABLE big_table NOLOGGING; prompt prompt      Please input rows number to fill into big_tableprompt     ============================================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_tableprompt     =====================================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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.