Upload data to oracle using sql loader

Let,
I have a table named user in my oracle database.
Table creation commands:

create table user
(
id number,
name varchar(10)
);

To insert large amount of data (e.g. 1 million) row into that table, we could use sql loader. This utility (SQL LOADER)is used to load data from other data source into Oracle.

Steps for doing this:

1. write .csv file user.csv. Its a simple text file with comma separated data.
data are written in the following way:

1,"user1"
2,"user2"

2. copy user.csv file to the server where oracle is running. You could use ftp or ssh for this.

Let, user.csv is copied in /root/mycsv folder

3. write user.ctl file

LOAD DATA
INFILE '/root/mycsv/user.csv'
append into table user
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(id,
name
)

Notes:

• The LOAD DATA statement is required at the beginning of the control file.
• The INFILE option specifies where the input file is located
• Specifying BADFILE is optional. If you specify, then bad records found during loading will be stored in this file.
• Specifying DISCARDFILE is optional. If you specify, then records which do not meet a WHEN condition will be written to this file.
• You can use any of the following loading option
  

a. INSERT : Loads rows only if the target table is empty

b. APPEND: Load rows if the target table is empty or not.

c. REPLACE: First deletes all the rows in the existing table and then, load rows.

d. TRUNCATE: First truncates the table and then load rows.

• 
  

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

This line indicates how the fields are separated in input file. Since in our case the fields are separated by “,” so we have specified “,” as the terminating char for fields. You can replace this by any char which is used to terminate fields. Some of the popularly use terminating characters are semicolon “;”, colon “:”, pipe “|” etc. TRAILING NULLCOLS means if the last column is null then treat this as null value, otherwise, SQL LOADER will treat the record as bad if the last column is null.

• (id,
  name
  )

• In this line specify the columns of the target table. Note how do you specify format for Date columns

4. execute the following command to load data from user.csv file to your database.

sqlldr userid=username/password@SIDname control='user.ctl' log='user.log'

5. If you don't know the SID name for oracle server, you can find it using following command:

echo $ORACLE_SID

if sid name was wrong, you'll get following error:

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified

If the csv file is too large and tablespace is full, you'll get following error:

SQL*Loader-605: Non-data dependent ORACLE error occurred -- load discontinued.

Check your .log file and if you find the following if tablespace is full

ORA-01653: unable to extend table username.tablename by X in tablespace yourtablespace

To resolve this, you need to add another table space. Execute the following command to see current status of your tablespace:

select * from dba_data_files where tablespace_name = 'yourtablespace;

You'll find current something like the following:

FILE_NAME
----------------------------------------------
/usr/local/oracle/oradata/webct/yourtablespace01.dbf

 FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
      11 yourtablespace                  4294967296     524288 AVAILABLE

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
        11 YES 4294967296     524288        65536 4294443008      524224

Now you have to add more table space using following command:

alter tablespace USERS add datafile '/usr/local/oracle/oradata/webct/yourtablespace02.dbf' size 1024M autoextend on next 512M maxsize 4096M;

See details solution here.