This is a procedure to create a huge table for testing quickly. I use it quite often when I need to build a large for testing. 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 with 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 key 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;

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