Oracle 12c – SQL*Loader

Oracle provides the utility SQL*loader for loading data from non-Oracle databases.  SQL*Loader is very flexible and can read a large range of flat files.  SQL*Loader can perform the following functions:


  1. Load data across network if files on different machine.
  2. Load data from multiple files in same session.
  3. Load data into multiple tables in same session.
  4. Specify character set.
  5. Selectively load data by values.
  6. Manipulate data before loading.
  7. Generate unique sequential keys.
  8. Use OS to access data files.
  9. Load data from disk, tape or named pipe.
  10. Load arbitrarily complex object-related data.
  11. Use secondary data files for loading LOBs and collections.
  12. Use conventional, direct path or external table loads.


SQL*Loader can be used with or without control file and you may encounter the following files during use:

  • Control – Defines data format and control behaviors.
  • Data – One or more data files containing info to be loaded.
  • Log – Logs performance and errors.
  • Bad – Contains all records not loaded because of errors.
  • Discard – Contains all records discarded by control file identified bypass.


SQL*Loader is invoked with the command SQLLDR.  You use parameters in the PARFILE or at the command line to control actions of SQLLDR.  If parameter appears in PARFILE and command line, command line is used.  Three sections of control files: PARFILE.


  1. Session wide info: global options, input data file location, data to be loaded.
  2. Contains INTO TABLE blocks (one or more)
  3. Optional third section can contain input data.


SQL*Loader can use two methods for loading data: Conventional Path and Direct Load option.  In Conventional Path SQL*Loader creates INSERT statement for data to be loaded and passes it to SQL Parser.   In Direct Load Path SQL*Loader bypasses the SQL and parser and loads data straight into the target table.  Direct Path is much faster than Conventional path, but conventional path is more flexible.

There are a few restrictions of Direct Path method:


  1. Cannot run concurrently with other transactions against a target table.
  2. Triggers on table do not fire.
  3. Data is written above high water mark, even if space is available below.
  4. Clustered tables are not supported.
  5. Foreign Key constraints are disabled during load.



Larry Catt


Part 7 – Building Oracle 12c development suite – Storage setup for Oracle Enterprise Linux 6.5 on Oracle VirtualBox

Storage setup for Oracle Enterprise Linux 6.5 on Oracle VirtualBox
Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process for configuration storage for an Oracle Enterprise Linux 6.5 installation in preparation for installation of Oracle 12c RDBMS.
NOTE: This process can be used for most Linux installations, regardless of original company or organization providing the base Linux OS.
1. Logon to your Windows desktop/laptop as an administrator and open Oracle VirtualBox.
2. Logon to your server as root user and create the oracle software owner with the following commands and enter your desired password for the oracle software owner:
login as: root
root@’s password:
Last login: Mon Sun 1 21:57:11 2015 from
[root@oel65 ~]# groupadd dba
[root@oel65 ~]# useradd oracle -g dba -G root
[root@oel65 ~]# passwd oracle
Changing password for user oracle.
New password:
BAD PASSWORD: it is based on a dictionary word
Retype new password:
passwd: all authentication tokens updated successfully.
[root@oel65 ~]#create the following directories for the oracle RDBMS storage.

3. Execute the following mkdir statements to create the appropriate storage of oracle RDBMS installation.
mkdir /opt/app
mkdir /opt/app/oracle
mkdir /opt/app/oracle_software
mkdir /u01
mkdir /u02
mkdir /u01/oradata
mkdir /u02/oradata
mkdir /opt/app/oracle_inventory
mkdir /opt/app/oracle_inventory/inventory

4. Finally change the ownership and permissions on the new storage location to the oracle software owner’s account.
chown oracle:dba /opt/app
chown oracle:dba /opt/app/oracle
chown oracle:dba /opt/app/oracle_software
chown oracle:dba /u01
chown oracle:dba /u02
chown oracle:dba /u01/oradata
chown oracle:dba /u02/oradata
chown oracle:dba /opt/app/oracle_inventory
chown oracle:dba /opt/app/oracle_inventory/inventory

5. This completes the setup of the storage for Oracle 12c.

Larry Catt, OCP