Oracle Utilities to Load Data into Database

The utilities are listed as follows:-

1) SQL* LOADER
2) DATA PUMP IMPORT and DATA PUMP EXPORT
3) Original Export Import

The merits/demerits of the utilities are listed as follows:-

1) SQL* LOADER
Merits:-
* It is the highly flexible allowing you to manipulate the data as it is being loaded

* One can use SQL*LOADER to break a single large data set into multiple sets of data during commit processing, significantly reducing the size of transactions processed by the load

* The direct path loading option performs the load very quickly.

* The rows which are discarded while loading are stored in the same format as the data file so that the .bad and .dsc file can be used again to load the data.

Demerit:-
* The script would work only with Oracle as its database


2) DATA PUMP IMPORT and DATA PUMP EXPORT

Merits:-
* Data Pump–60% faster than Export (single stream)

* 15X-20X faster than Import (single stream)

Demerit:-

* Though it is faster than the Original Import/Export wizard it has more overheads as compared to SQL*Loader. While loading the data on a recursive basis more steps need to be followed as compared to a single line command of SQL*LOADER script.

3) Original Export Import

Merits:-

* It is most useful when we want to import the data without creating the tables as the wizard would itself import the table structure.

* Useful for non-iterative jobs

Demerit:-

* As large time is required to load large data not prescribed when large data is to be imported