Data Loader…

Fastest Data Loading

In this paper I am going to explain you about data loading Techniques

1. Sql Loader
2. Direct Load Insert
3. Guidelines for Sql Loader and Direct Load

Sql Loader

Sql Loader loads data from external files into oracle database tables

Direct Load Insert

It can be used to copy data within same oracle database. Bypass the buffer cache and speed up the process by writing data directly into the data files.

Direct Load inserts only supports INSERT … SELECT syntax only. ‘Insert values(…)’ wont support. You could see parallel and Serial Inserts in the Direct Load Insert Method.

Data will be inserted beyond high water mark of the table segment or each partition segment. High water mark is the level in which block have never been written to that area.

Serial Direct Load Insert

Example:

INSERT /*+ APPEND */ INTO emp

NOLOGGING

SELECT * FROM sarith.emp;

COMMIT;

Parallel Direct Load Insert

Each parallel execution server allocates a new temporary segment. When a statement executes, the parallel execution coordinator merges the new temporary segments into the primary table segment.

Example

ALTER SESSION ENABLE PARELLEL DML;

INSERT /*+PARALLEL(sarith.emp,3) */

INTO sarith.emp NOLOGGING

SELECT * FROM hr.emp_bad;

ALTER SESSION command must be executed at the beginning of the transaction

3 Indicates the number of parallel process

SQL Loader

Sql Loader loads data from external files into oracle database Data files

1. It can use more than one input files
2. Several input can be merged for loading
3. Input field can be fixed or Variable
4. Data can be loaded from diff types of media like Disk, Tape, named Pipes
5. Data can be Loaded into several tables in one Run
6. Column value can be auto generated
7. Data Can be loaded directly into data file

Example

$sqlldr scott/tiger \

>control = example.ctl \

>log=example.log direct=y

An example for Control File

  -- This is a sample control file
   LOAD DATA
   INFILE 'sample.dat'
   BADFILE 'sample.bad'
   DISCARDFILE 'sample.dsc'
   APPEND
   INTO TABLE emp
   WHEN (57) = '.'
   TRAILING NULLCOLS
  (hiredate SYSDATE,
      deptno POSITION(1:2)  INTEGER EXTERNAL(2)
              NULLIF deptno=BLANKS,
       job    POSITION(7:14)  CHAR  TERMINATED BY WHITESPACE
              NULLIF job=BLANKS  "UPPER(:job)",
       mgr    POSITION(28:31) INTEGER EXTERNAL
              TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
       ename  POSITION(34:41) CHAR
              TERMINATED BY WHITESPACE  "UPPER(:ename)",
       empno  POSITION(45) INTEGER EXTERNAL
              TERMINATED BY WHITESPACE,
       sal    POSITION(51) CHAR  TERMINATED BY WHITESPACE
              "TO_NUMBER(:sal,'$99,999.99')",
       comm   INTEGER EXTERNAL  ENCLOSED BY '(' AND '%'
              ":comm * 100"
    )

Infile can be a CSV file or any file that must have a proper format.

Oracle FlashBack Technology

The Oracle Flashback Technology

Oracle Flashback Technology provides some useful ways to get back your lost data without doing whole database or the whole datafile or a large portion of the database restoration from backup database.You do not have to do Point in time recovery. The flashback feature of Oracle 10g is less risk and Efficient of doing table recovery.

Example is from Arjudba Blog

Here is an example for your better understanding,

SQL> select * from t;
         A
----------
         1
       100
        10

SQL> insert into t values(2);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t;
         A
----------
         1
       100
        10
         2

Flashback Table
SQL> flashback table t to timestamp systimestamp-interval ’1′ minute;

Flashback complete.

SQL> select * from t;
         A
----------
         1
       100
        10

Flashback Versions Query
You can also keep track of change data over time suppose when one row is upadted when one row is deleted and when one row is inserted by following query,

select versions_starttime, versions_endtime, versions_xid,versions_operation,a
from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;

SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation,a
  from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;  

Output is,

VERSIONS_STARTT VERSIONS_ENDTIME     VERSIONS_XID     V          A
--------------- -------------------- ---------------- - ----------
09-APR-08 02.47 09-APR-08 02.47.26 P 0100090035010000 I          5
.16 PM          M

09-APR-08 02.47                      0700100035010000 D          5
.26 PM

                                                               100
                                                                10
                                                                 1

SQL> insert into t values(200);
1 row created.

SQL> commit;
Commit complete.

SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation,a
  from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;  

VERSIONS_STARTT VERSIONS_ENDTIME     VERSIONS_XID     V          A
--------------- -------------------- ---------------- - ----------
09-APR-08 02.47 09-APR-08 02.47.26 P 0100090035010000 I          5
.16 PM          M

09-APR-08 02.47                      0700100035010000 D          5
.26 PM

09-APR-08 02.49                      0700140035010000 I        200
.35 PM

                                                                10
                                                                 1

                                                               100

6 rows selected.

You can find out the changes over time by ,

select a, versions_starttime, versions_endtime
from t versions between timestamp
to_date(’7/3/20078 13:33:54′,’mm/dd/yyyy hh24:mi:ss’)
and to_date(’7/3/2008 13:37:55′,’mm/dd/yyyy hh24:mi:ss’)

Flashback Transaction Query
You can check undo_sql by,
SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = ‘….’;

Flashback Query
SQL> select * from flash;
A
———-
1
2

SQL> insert into flash values(3);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from flash as of timestamp systimestamp-interval ’1′ minute;
A
———-
1
2

SQL> select * from flash;
A
———-
1
2
3

usefull Links

Restore Old Data

Oracle FlashBack Technology

Flashback Table