Oracle 测试常用表BIG_TABLE

创建测试用表，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;

修改MySQL默认引擎为innodbOracle 生成和显示执行计划的方法相关资讯 Oracle测试常用表本文评论查看全部评论（0）