Common Oracle test table BIG_TABLE

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 ---- CreateatesttableforOracle10g -- Fi Based on Oracle10g

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 -- ======================================== =================-- Create a test table for Oracle 10g -- Fi

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: -- ===================================================== ======= promptpromptCreate 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 promptPlease input rows number to fill into big_tableprompt ======================== ============== DECLAREl_cnt NUMBER; rows NUMBER: = & 1; BEGININSERT/* + append */INTO big_tableSELECT rownum,. * FROM all_objects a; l_cnt: = SQL % ROWCOUNT; COMMIT; WHILE (l_cnt <l_rows) LOOPINSERT/* + APPEND */INTO big_tableSELECT rownum + l_cnt, owner, object_name, subobject_name, object_id, data_object_id, object_type, created, encrypted, TIMESTAMP, status, temporary, generated, secondaryFROM big_tableWHERE rownum <= l_rows-l_cnt; l_cnt: = l_cnt + SQL % ROWCOUNT; COMMIT; end loop; END; /prompt promptAdd primary key for big table prompt ============================ ====== alter table big_table add constraint big_table_pk primary key (id ); prompt promptGather statistics for big_tableprompt ============================== = BEGINdbms_stats.gather_table_stats (ownname => USER, tabname => 'Big _ table', method_opt => 'for all indexed columns', cascade => TRUE); END; /prompt promptcheck 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: -- ===================================================== ======= promptpromptCreate 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 promptPlease input rows number to fill into big_tableprompt ======================== ============== DECLAREl_cnt NUMBER; rows NUMBER: = & 1; BEGININSERT/* + append */INTO big_tableSELECT rownum,. * FROM all_objects a; l_cnt: = SQL % ROWCOUNT; COMMIT; WHILE (l_cnt <l_rows) LOOPINSERT/* + APPEND */INTO big_tableSELECT rownum + l_cnt, owner, object_name, subobject_name, object_id, struct, object_type, created, primary, TIMESTAMP, status, temporary, generated, secondary, namespace, edition_nameFROM big_tableWHERE rownum <= l_rows-l_cnt; l_cnt: = l_cnt + SQL % ROWCOUNT; COMMIT; end loop; END; /prompt promptAdd primary key for big table prompt ============================ ====== alter table big_table add constraint big_table_pk primary key (id ); prompt promptGather statistics for big_tableprompt ============================== = BEGINdbms_stats.gather_table_stats (ownname => USER, tabname => 'Big _ table', method_opt => 'for all indexed columns', cascade => TRUE); END; /prompt promptcheck total rows for big_table prompt ============================ ==== select count (*) FROM big_table;