Oracle 测试常用表BIG_TABLE

创建测试用表，DBA经常用到，通常都是基于dba_objects来创建的比较多。本文根据Tom大师的big_table进行了整理，供大家参考。 一、基于Oracle 10g下的big_table –==============================================– Create a test table for Oracle 10g– Fi

创建测试用表，，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 : –==============================================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;l_rows NUMBER := &1;BEGININSERT /*+ append */INTO big_tableSELECT rownum, a.*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,last_ddl_time,TIMESTAMP,status,temporary,generated,secondaryFROM big_tableWHERE rownum 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;

二、基于Oracle 11g下的big_table

–==============================================– 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;l_rows NUMBER := &1;BEGININSERT /*+ append */INTO big_tableSELECT rownum, a.*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_n本文来源[email protected]搞@^&代*@码2网ame,object_id,data_object_id,object_type,created,last_ddl_time,TIMESTAMP,status,temporary,generated,secondary,namespace,edition_nameFROM big_tableWHERE rownum 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;

喜欢 (0)赏

[搞代码]

分享 (0)