Oracle Test common Table big

The

Creates test tables that are often used by DBAs, often based on dba_objects to create more. This paper is based on the big_table of Tom Master, for everyone's reference.
One, Oracle 10g based big_table

--==============================================--Create a test table for Oracle 10g--File:cr_big_tb_10g.sql--A Uthor:robinson--blog:http://blog.csdn.net/robinson_0612--============================================== prompt P 
ROMPT 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 # 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_obje
        CTS A;
        L_CNT: = Sql%rowcount;
        COMMIT; while (L_cnt < l_rows) Loop INSERT/*+ APPEND * *NTO 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_t
 
Able ADD CONSTRAINT BIG_TABLE_PK PRIMARY KEY (ID); Prompt prompt Gather statistics forbig_table prompt ===================================== BEGIN dbms_stats.gather_table_stats (ownname => USER, TabName => ' big_table ', method_opt => ' fo
R all indexed columns ', cascade => TRUE); 
End; /prompt prompt check total rows for big_table prompt ==================================== SELECT COUNT (*) F ROM big_table;

Two, Oracle 11g based big_table

--==============================================--Create a test table for Oracle 11g--File:cr_big_tb_11g.sql--A Uthor:robinson--blog:http://blog.csdn.net/robinson_0612--============================================== prompt P 
ROMPT 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 # 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_obje
        CTS 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_t Able ADD CONstraint BIG_TABLE_PK PRIMARY KEY (ID); Prompt prompt Gather statistics for big_table prompt ===================================== BEGIN dbms_st
                                     Ats.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 (*) F ROM big_table;