SQL*LOADER

usually export reads the data from the database and then writes to buffer and then loads into dumpfile.
similarly import reads from the dump file and then buffer and then places in database.

usually export utility is understood by import only but not by any other files.consider a flat file 'text file' then using exp/expdb we cant  import data.so SQL*LOADER introduced & it contains very powerfull engine to export the data and then we can import it.

Different types of files are there in using SQL*loader and to import data we have utility $sqlldr

SQL*loader always act as import utility not an export utility.

The flat file provided as input to SQL*loader is called as 'Inputfile/datafile'.

suppose a flat file data is importing with sql loader to the database we have no guarantee
that all rows added to the database.Because there is some mismatch to the datatype or not enough space.For this SQL loader maintains a file containing these not imported rows.That file is called as 'bad file'.

NOTE:-here SQL*loader itself rejecting the rows.

     

The location of sqlldr is 

[oracle@cloneapps bin]$ ls -ltr sqlldr

-rwxr-x--x 1 oracle dba 1479830 Dec 20 15:00 sqlldr

Using SQL loader

Steps:

1.        create a flat file(eg .dat file)

$vi product.dat

 100,raw, 102

 101,food, 103

 209,wood, 203

: wq !

2.        create a controlfile

$vi product.ctl

Load data

Infile ‘/u01/user18/product.dat’

Into table items

Fields terminated by “,”

(Itemid, name, qty)

: wq! //save and quit

        

3.        create table with same name and columns specified in the control file

 eg: Suppose we have a schema in that create one table

  sql>create table items (itemid number, name varchar2(20),qty number);

 

4.        load the data from product.dat file to database table:

 $sqlldr userid=demo/demo control=product.ctl log=product. log