How to insert large amount of data in oracle table faster way

First - how can you optimize your PL/SQL's performance:

•Disable indexes and any other constraints on target tables before you begin your load and re-enable them after you are done
•Don't commit at the very end - have commit points to free-up rollback segments
Second - don't do the insert with PL/SQL. Use BulkLoading. you can easily find lots of info on BulkLoading if you Google for "oracle sql loader"


===========================================

For this can be Use Direct Load Insert
——————————————————-
Direct Load Insert is a faster way of running an INSERT statement.Direct Load Insert
differs from Conventional Insert in that it bypasses the buffer cache.

To use Direct Load Insert, add the APPEND hint to your INSERT statement. like below…..

INSERT /*+ APPEND*/
INTO target_table
SELECT * FROM source_table

Need to know for Direct load Insert
—————————————————
1. Data is appended to the table. Existing free space is not re-used.
2. Direct Load Insert only works for INSERT INTO .. SELECT …. Inserts
3. Direct Load Insert uses rollback segments to maintain indexes as the data is loaded
4. Direct Load Insert can be run with a NOLOGGING option making it even faster.
5. Direct Load Insert locks the table in exclusive mode. No other session can insert, update, or delete data, or maintain any indexes.
6. Referential Integrity (Foreign Key) constraints and triggers must be disabled before running Direct Path Insert
7. Direct Load Insert cannot occur on:
o Index Organised Tables
o Tables with LOB Columns
o Tables with Object Columns
o Clustered Tables

DEMO
=====

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Jul 31 12:28:13 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn basel2@test107
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> set timing on
SQL> set time on
12:28:41 SQL>
12:28:42 SQL>
12:28:42 SQL> create table direct_load_insert as select * from TABLE_OF_VIEW_BACKUP
12:30:57   2  where brancd=0 ;

Table created.

Elapsed: 00:00:04.03
12:31:28 SQL>
12:31:31 SQL> desc  direct_load_insert
Name                                      Null?    Type
—————————————– ——– —————————-
BRANCD                                             VARCHAR2(3)
ACTYPE                                             VARCHAR2(3)
ACTNUM                                             VARCHAR2(12)
CURBAL                                             NUMBER
LONCON                                             VARCHAR2(3)
ACTTIT                                             VARCHAR2(60)
CUSCOD                                             VARCHAR2(10)
SHDESC                                             VARCHAR2(10)
OPNDAT                                             DATE
EXPDAT                                             DATE
SECURITY_BAL                                       NUMBER
SECURITY_TYPE                                      VARCHAR2(4000)
VALDAT                                             DATE
CATGRY                                             VARCHAR2(1)
VALPRD                                             NUMBER(4)
LCAAMT                                             NUMBER(16,3)
REMAMT                                             NUMBER(16,3)
BANCOD                                             VARCHAR2(3)
CMPIND                                             VARCHAR2(1)
REMARK                                             VARCHAR2(50)

12:31:48 SQL>
12:31:50 SQL>
12:31:51 SQL> INSERT /*+ APPEND*/
12:32:19   2  INTO direct_load_insert
12:32:44   3  SELECT * FROM TABLE_OF_VIEW_BACKUP ;

3706656 rows created.

Elapsed: 00:01:13.31
12:34:17 SQL>
12:34:23 SQL>
12:34:23 SQL>
12:34:23 SQL> ROLLBACK ;

Rollback complete.

Elapsed: 00:00:00.06
12:34:53 SQL>
12:34:54 SQL>
12:34:54 SQL> INSERT INTO direct_load_insert
12:35:31   2  SELECT * FROM TABLE_OF_VIEW_BACKUP ;

3706656 rows created.

Elapsed: 00:01:53.36
12:37:30 SQL>
12:38:06 SQL>
12:38:06 SQL> ROLLBACK
12:39:44   2  /

Rollback complete.

Elapsed: 00:01:25.35
12:41:12 SQL>
12:41:14 SQL>
12:41:14 SQL> DROP TABLE direct_load_insert ;

Table dropped.

Elapsed: 00:00:28.17
12:41:57 SQL>
12:41:59 SQL>
12:41:59 SQL>