創建測試用表,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;