Kokei DBA Labs

This is a procedure I wrote in order to help me to create a huge table for testing quickly( The idea of the code is borrowed from Tom Kyte’s book). This stored proc takes two parameters, table_name for the name of the table to be created and table_size for the number of rows to be loaded to the newly created table . Some of the advantages of this procedure are that this stored proc create table with nologging and load the data to the table with APPEND hint.
Therefore this stored proc can create a huge table quickly because of minminal redo log generation.

CREATE OR REPLACE PROCEDURE Build_Table
     (Table_Name  IN VARCHAR,
      Table_Size  NUMBER)
IS
  sql_stmt  VARCHAR(2000);
  l_cnt     NUMBER := 0;
  l_Rows    NUMBER := Table_Size;
BEGIN
  sql_stmt := ‘create table ‘  –create table structure
             
              ||Table_Name
              ||’ nologging
as select rownum id,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
  from all_objects a where 1=0′;
 
  EXECUTE IMMEDIATE sql_stmt;
 
  COMMIT;
 
  sql_stmt := ‘insert /*+ APPEND */ into ‘ –load the data
             
              ||Table_Name
              ||’ select rownum,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
          from all_objects
  where rownum<=:1′;
 
  EXECUTE IMMEDIATE sql_stmt
   USING l_Rows;
 
  l_cnt := l_cnt + SQL%ROWCOUNT;
 
  COMMIT;
 
  WHILE (l_cnt < l_Rows) LOOP
    sql_stmt := ‘insert /*+ APPEND */ into ‘
                ||Table_Name
                ||’ select rownum+:1,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
          from ‘
                ||Table_Name
                ||’ where rownum <= :2-:3′;
   
    EXECUTE IMMEDIATE sql_stmt
     USING l_cnt,l_Rows,l_cnt;
   
    l_cnt := l_cnt + SQL%ROWCOUNT;
   
    COMMIT;
  END LOOP;
 
  sql_stmt := ‘alter table ‘ –create the primary after loading the data
             
              ||Table_Name
              ||’ add constraint ‘
              ||Table_Name
              ||’_pk primary key(id)’;
 
  EXECUTE IMMEDIATE sql_stmt;
 
  sql_stmt := ‘ BEGIN dbms_stats.gather_table_stats –update the table statistics
   ( ownname    => SYS_CONTEXT (”USERENV”, ”CURRENT_USER”),
     tabname    =>”’
              ||Upper(Table_Name)
              ||”’,
     cascade    => TRUE ); end;’;
 
  EXECUTE IMMEDIATE sql_stmt;
END;
CREATE OR REPLACE PROCEDURE Build_Table
     (Table_Name  IN VARCHAR,
      Table_Size  NUMBER)
IS
  sql_stmt  VARCHAR(2000);
  l_cnt     NUMBER := 0;
  l_Rows    NUMBER := Table_Size;
BEGIN
  sql_stmt := ‘create table ‘  –create table structure
              ||Table_Name
              ||’ nologging
as select rownum id,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
  from all_objects a where 1=0′;
 
  EXECUTE IMMEDIATE sql_stmt;
 
  COMMIT;
 
  sql_stmt := ‘insert /*+ APPEND */ into ‘ –load the data
              ||Table_Name
              ||’ select rownum,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
          from all_objects
  where rownum<=:1′;
 
  EXECUTE IMMEDIATE sql_stmt
   USING l_Rows;
 
  l_cnt := l_cnt + SQL%ROWCOUNT;
 
  COMMIT;
 
  WHILE (l_cnt < l_Rows) LOOP
    sql_stmt := ‘insert /*+ APPEND */ into ‘
                ||Table_Name
                ||’ select rownum+:1,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
          from ‘
                ||Table_Name
                ||’ where rownum <= :2-:3′;
   
    EXECUTE IMMEDIATE sql_stmt
     USING l_cnt,l_Rows,l_cnt;
   
    l_cnt := l_cnt + SQL%ROWCOUNT;
   
    COMMIT;
  END LOOP;
 
  sql_stmt := ‘alter table ‘  –create the primary after loading the data
              ||Table_Name
              ||’ add constraint ‘
              ||Table_Name
              ||’_pk primary key(id)’;
 
  EXECUTE IMMEDIATE sql_stmt;
 
  sql_stmt := ‘ BEGIN dbms_stats.gather_table_stats –update the table statistics
   ( ownname    => ”SYSTEM”,
     tabname    =>”’
              ||Upper(Table_Name)
              ||”’,
     cascade    => TRUE ); end;’;
 
  EXECUTE IMMEDIATE sql_stmt;
END;

References
http://www.dbspecialists.com/files/presentations/load_faster.html