您当前的位置: 首页 > 数据库教程 > Oracle教程 > Oracle 测试常用表BIG_TABLE

Oracle 测试常用表BIG_TABLE

作者:不详 来源:网络 发布时间: 2014-08-26 11:16 点击:
创建测试用表,DBA经常用到,通常都是基于dba_objects来创建的比较多。本文根据Tom大师的big_table进行了整理,供大家参考。 一、基于Oracle 10g下的big_table --============================================== -- Create a test table for Oracle 10g -- File : cr_

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;

  二、基于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;
分享到:
本文"Oracle 测试常用表BIG_TABLE"由远航站长收集整理而来,仅供大家学习与参考使用。更多网站制作教程尽在远航站长站。
顶一下
(0)
0%
踩一下
(0)
0%
[点击 次] [返回上一页] [打印]
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 密码: 验证码:
关于本站 - 联系我们 - 广告合作 - 网站声明 - 友情连接- 网站地图 - 站点地图 - 返回顶部
Copyright © 2007-2013 www.yhzhan.com(远航站长). All Rights Reserved .
远航站长:为中小站长提供最佳的学习与交流平台,提供网页制作与网站编程等各类网站制作教程.
官方QQ:445490277 网站群:26680406 网站备案号:豫ICP备07500620号-4