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;