【ORACLE】Oracle测试常用表BIG_TABLE创建脚本

Oracle 测试常用表BIG_TABLE创建脚本

DBA经常用到的创建测试用表，通常都是基于dba_objects来创建的比较多。

本文根据Tom大师的big_table进行了整理，供大家参考。

一、基于Oracle 10g下的big_table.sql

点击(此处)折叠或打开

1. --==============================================
   
2. -- Create a test table for Oracle 10g
   
3. -- File : cr_big_tb_10g.sql
   
4. -- Author : Robinson
   
5. --==============================================
   
6.   
   
7. prompt
   
8. prompt Create a big table from all_objects
   
9. prompt ======================================
   
10. CREATE TABLE big_table
    
11. AS
    
12. SELECT ROWNUM id, a.*
    
13. FROM all_objects a
    
14. WHERE 1=0;
    
15.    
    
16. prompt
    
17. prompt Modify table to nologgming mode
    
18. prompt ==========================
    
19. ALTER TABLE big_table NOLOGGING;
    
20.    
    
21. prompt
    
22. prompt Please input rows number to fill into big_table
    
23. prompt ============================================
    
24. DECLARE
    
25.         l_cnt NUMBER;
    
26.         l_rows NUMBER := &1;
    
27. BEGIN
    
28.         INSERT /*+ append */
    
29.         INTO big_table
    
30.                 SELECT rownum, a.*
    
31.                 FROM all_objects a;
    
32.         l_cnt := SQL%ROWCOUNT;
    
33.         COMMIT;
    
34.         WHILE (l_cnt < l_rows)
    
35.         LOOP
    
36.                 INSERT /*+ APPEND */
    
37.                 INTO big_table
    
38.                         SELECT rownum + l_cnt
    
39.                              ,owner
    
40.                              ,object_name
    
41.                              ,subobject_name
    
42.                              ,object_id
    
43.                              ,data_object_id
    
44.                              ,object_type
    
45.                              ,created
    
46.                              ,last_ddl_time
    
47.                              ,TIMESTAMP
    
48.                              ,status
    
49.                              ,temporary
    
50.                              ,generated
    
51.                              ,secondary
    
52.                         FROM big_table
    
53.                         WHERE rownum <= l_rows - l_cnt;
    
54.                 l_cnt := l_cnt + SQL%ROWCOUNT;
    
55.                 COMMIT;
    
56.         END LOOP;
    
57. END;
    
58. /
    
59.    
    
60. prompt
    
61. prompt Add primary key for big table
    
62. prompt =====================================
    
63. ALTER TABLE big_table ADD CONSTRAINT
    
64. big_table_pk PRIMARY KEY (id);
    
65.    
    
66. prompt
    
67. prompt Gather statistics for big_table
    
68. prompt =====================================
    
69. BEGIN
    
70.         dbms_stats.gather_table_stats(ownname => USER,
    
71.                                      tabname => 'BIG_TABLE',
    
72.                                      method_opt => 'for all indexed columns',
    
73.                                      cascade => TRUE);
    
74. END;
    
75. /
    
76.    
    
77. prompt
    
78. prompt check total rows for big_table
    
79. prompt ====================================
    
80. 
    
81. SELECT COUNT(*) FROM big_table;

二、基于Oracle 11g下的big_table.sql

点击(此处)折叠或打开

1. --==============================================
   
2. -- Create a test table for Oracle 11g
   
3. -- File : cr_big_tb_11g.sql
   
4. -- Author : Robinson
   
5. --==============================================
   
6.   
   
7. prompt
   
8. prompt Create a big table from all_objects
   
9. prompt ======================================
   
10. CREATE TABLE big_table
    
11. AS
    
12. SELECT ROWNUM id, a.*
    
13. FROM all_objects a
    
14. WHERE 1=0;
    
15.    
    
16. prompt
    
17. prompt Modify table to nologgming mode
    
18. prompt ==========================
    
19. ALTER TABLE big_table NOLOGGING;
    
20.    
    
21. prompt
    
22. prompt Please input rows number to fill into big_table
    
23. prompt ============================================
    
24. DECLARE
    
25.         l_cnt NUMBER;
    
26.         l_rows NUMBER := &1;
    
27. BEGIN
    
28.         INSERT /*+ append */
    
29.         INTO big_table
    
30.                 SELECT rownum, a.*
    
31.                 FROM all_objects a;
    
32.         l_cnt := SQL%ROWCOUNT;
    
33.         COMMIT;
    
34.         WHILE (l_cnt < l_rows)
    
35.         LOOP
    
36.                 INSERT /*+ APPEND */
    
37.                 INTO big_table
    
38.                         SELECT rownum + l_cnt
    
39.                              ,owner
    
40.                              ,object_name
    
41.                              ,subobject_name
    
42.                              ,object_id
    
43.                              ,data_object_id
    
44.                              ,object_type
    
45.                              ,created
    
46.                              ,last_ddl_time
    
47.                              ,TIMESTAMP
    
48.                              ,status
    
49.                              ,temporary
    
50.                              ,generated
    
51.                              ,secondary
    
52.                              ,namespace
    
53.                              ,edition_name
    
54.                         FROM big_table
    
55.                         WHERE rownum <= l_rows - l_cnt;
    
56.                 l_cnt := l_cnt + SQL%ROWCOUNT;
    
57.                 COMMIT;
    
58.         END LOOP;
    
59. END;
    
60. /
    
61.    
    
62. prompt
    
63. prompt Add primary key for big table
    
64. prompt =====================================
    
65. ALTER TABLE big_table ADD CONSTRAINT
    
66. big_table_pk PRIMARY KEY (id);
    
67.    
    
68. prompt
    
69. prompt Gather statistics for big_table
    
70. prompt =====================================
    
71. BEGIN
    
72.         dbms_stats.gather_table_stats(ownname => USER,
    
73.                                      tabname => 'BIG_TABLE',
    
74.                                      method_opt => 'for all indexed columns',
    
75.                                      cascade => TRUE);
    
76. END;
    
77. /
    
78.    
    
79. prompt
    
80. prompt check total rows for big_table
    
81. prompt ====================================
    
82. 
    
83. SELECT COUNT(*) FROM big_table;

三、说明
1、该脚本根据Tom大师的原代码big_table整理而成。
2、Oracle 11g all_objects 比Oracle 10g 多出两列，因此使用了2个不同的版本。
3、big_table的id列为唯一值，并在之上创建了primary key。
4、对于该表测试redo等相关信息是应启用logging模式。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/618091/viewspace-2123750/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/618091/viewspace-2123750/