Import data via direct load using the CREATE TABLE AS SELECT statement

Use direct load to import data from table tbl1 into other tables.

1. Create table tbl1 and write test data.

   obclient [test]> CREATE TABLE tbl1(c1 int);
   obclient [test]> INSERT INTO tbl1 VALUES (1),(2),(3);
   obclient [test]> SELECT * FROM tbl1;
   

2. Use CREATE TABLE AS SELECT to import data into tbl2.

   • Full mode:

     obclient [test]> CREATE /*+ direct(true, 0, 'full') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;
     

   • Incremental mode:

     obclient [test]> CREATE /*+ direct(true, 0, 'inc') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;
     obclient [test]> CREATE /*+ direct(true, 0, 'inc_replace') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;
     

   • Optional: Use APPEND (equivalent to DIRECT(true, 0)):

     obclient [test]> CREATE /*+ append parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;
     

3. If no hint is specified, you can control the default mode through the parameter:

   obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';
   obclient [test]> CREATE TABLE tbl2 AS SELECT * FROM tbl1;
   

   or:

   obclient [test]> ALTER SYSTEM SET default_load_mode ='INC_DIRECT_WRITE';
   obclient [test]> CREATE TABLE tbl2 AS SELECT * FROM tbl1;