Secrets for optimizing Oracle table inserts

When loading large-volumes of data, you have several choices:

1 - Tune INSERTS - Parallelized programs doing concurrent INSERT statements

2 - Tune SQL*Loader - Using sqlldr Direct Load, and adjusting parameters improves INSERT performance.

3 - Tune imports - Use Oracle Data Pump (Formally Oracle import utility) - Here are tips for hypercharging Oracle import.

Optimizing Oracle INSERT performance

When using standard SQL statements to load Oracle data tables, there are several tuning approaches:

a - Manage segment header contention for parallel inserts - Make sure to define multiple freelist (or freelist groups) to remove contention for the table header. Multiple freelists add additional segment header blocks, removing the bottleneck. You can also use Automatic Segment Space Management (bitmap freelists) to support parallel DML, but ASSM has some limitations.

Read more about secrets for optimizing Oracle table inserts here:
http://oracle-tips.c.topica.com/maalfseabGRCBcilq9pb/

How to Create Oracle Database Manually

CREATING THE DATABASE MANUALLY

This method involves typing the create database statement within SQL*DBA.
Using this method allows for more flexibility such as specifying
the MAXDATAFILES parameter or specifying multiple SYSTEM tablespace
database files. However by doing this manually there is also a greater
possibility of syntax errors. In addition there is no logfile automatically
created to record the options which have been specified.

Steps for Method I:

1. Set unix environment (SEE SETTING UNIX EVIRONMENT SECTION ABOVE).

2. Create a new init.ora for your new database by copying the default
   one provided by Oracle:

   % cp $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/initNEW_NAME.ora

3. Change db_name parameter in the new initNEW_NAME.ora from DEFAULT to
   the new database name.

4. Startup sql*dba in line mode:

      sqldba lmode=y

5. Connect to the instance, and startup in a 'NOMOUNT' state:

      SQLDBA> connect internal
      Connected.
      SQLDBA> startup nomount
      ORACLE instance started.
      SQLDBA>

6. Refer to the SQL Language Reference Guide for the 'CREATE DATABASE'
   statement syntax - page 4-148.

   Here is a sample create database statement:

      SQLDBA> create database NEW_NAME
           2> logfile group 1 ('oracle_home/dbs/log1NEW_NAME.dbf') size 500K,
           3>         group 2 ('oracle_home/dbs/log2NEW_NAME.dbf') size 500K
           4> datafile 'oracle_home/dbs/dbsNEW_NAME.dbf' size 20M
           5> maxdatafiles 50;

7. Once completed run catalog.sql located in the oracle_home/rdbms/admin
   directory. This script must be run under the 'SYS' user or connected
   'internal'.

   NOTE: catproc.sql must also be run if you have the procedural option
         installed.

      SQLDBA>@oracle_home/rdbms/admin/catalog.sql
      SQLDBA>@oracle_home/rdbms/admin/catproc.sql

8. After the database has been created, the SYSTEM tablespace and SYSTEM
   rollback segment will exist. However, a second rollback segment in the
   SYSTEM tablespace must be created and activated before any other
   tablespaces can be created in the database (Refer to SQL Lanuguage
   Reference Manual for full syntax).

      Creating the rollback segment:

        SYNTAX: CREATE ROLLBACK SEGMENT system2
                TABLESPACE SYSTEM
                STORAGE (...);

      Activating the rollback segment:

        SYNTAX: ALTER ROLLBACK SEGMENT system2 ONLINE;

9. Modify the /etc/oratab file by adding the new database name.  This
   is used by dbstart to startup all databases with a 'Y' entry in this
   file.  (See page 4-17 of Oracle for Unix technical Reference Guide).

Source:http://www.fors.com/orasupp/unix/13481_1.HTM