Copy Huge table..

CREATE OR REPLACE PROCEDURE copy_table
IS
CURSOR emp_cur IS SELECT /*+ PARALLEL (t1)*/ * FROM emp t1;

TYPE fetch_array_type IS TABLE OF emp_cur%ROWTYPE;
t_array fetch_array_type;

BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL DML PARALLEL 4';

OPEN emp_cur ;
LOOP
FETCH emp_cur BULK COLLECT INTO t_array LIMIT 10000;
FORALL i IN 1..t_array.COUNT
INSERT /*+ APPEND */ INTO empl1 VALUES t_array(i) ;
EXIT WHEN emp_cur%NOTFOUND;
COMMIT;
END LOOP;
COMMIT;
CLOSE emp_cur;
END;
/

Export Table data to CSV file

CREATE OR REPLACE PROCEDURE dump_table_to_csv (
p_tname IN VARCHAR2,
p_dir IN VARCHAR2,
p_filename IN VARCHAR2
)
IS
l_output UTL_FILE.file_type;
l_thecursor INTEGER DEFAULT DBMS_SQL.open_cursor;
l_columnvalue VARCHAR2 (4000);
l_status INTEGER;
l_query VARCHAR2 (1000) DEFAULT 'SELECT * FROM ' p_tname;
l_colcnt NUMBER := 0;
l_separator VARCHAR2 (1);
l_desctbl DBMS_SQL.desc_tab;
BEGIN
l_output := UTL_FILE.fopen (p_dir, p_filename, 'w');
EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format=''dd-mon-yyyy hh24:mi:ss''';
DBMS_SQL.parse (l_thecursor, l_query, DBMS_SQL.native);
DBMS_SQL.describe_columns (l_thecursor, l_colcnt, l_desctbl);
FOR i IN 1 .. l_colcnt
LOOP
UTL_FILE.put (l_output,
l_separator '"' l_desctbl (i).col_name '"'
);
DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 4000);
l_separator := ',';
END LOOP;
UTL_FILE.new_line (l_output);
l_status := DBMS_SQL.EXECUTE (l_thecursor);
WHILE (DBMS_SQL.fetch_rows (l_thecursor) > 0)
LOOP
l_separator := '';
FOR i IN 1 .. l_colcnt
LOOP
DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_columnvalue);
UTL_FILE.put (l_output, l_separator l_columnvalue);
l_separator := ',';
END LOOP;
UTL_FILE.new_line (l_output);
END LOOP;
DBMS_SQL.close_cursor (l_thecursor);
UTL_FILE.fclose (l_output);
EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format=''dd-MON-yy'' ';
EXCEPTION
WHEN OTHERS
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format=''dd-MON-yy'' ';
RAISE;
END;
/

How SQL Works

When you fire the SQL Query, Basically there are three steps involved in it.
1) Parse
2) Execution
3) Fetch
In Step(1) : During the parse phase, the SQL statement is passed from user process to Oracle and a parsed representation of the SQL statement is loaded into a shared SQL area. Parsing is the process of
1) Translating SQL statement, verifying it to be a valid statement
2) Performing data distionary lookups to check table and column defination
3) Acquiring parse locks on the required objects so that their defination do not change during the statement's parsing.
4) Checking previleges to access referenced schema objects.
5) Determining the optimal execution plan for the statement.
6) Loading it into a shared SQL area.A SQL statement is parsed only if a shared SQL area for an identical SQL statement does not exist in the shared pool. In this case a new shared SQL area is allocated and the statement is parsed.
Step(2) ...During execute phase, at this point Oracle has all necessary information and resources, so the statement is executed.
Step(3) .. In the fetch phase, rows are selected and ordered(if requested by the query), and each successive fetch retrieves another row of the result, until the last row has been fetched.

Order Cluase for Sequence

ORDER Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.

Refresh ON COMMIT not working for Materialized View using Db link

"ON COMMIT" works with local tables only - you cannot use DB Links.Somehow this is not properly documented in the Oracle Manuals.