Oracle SQL*Loader (sqlldr) Utility Tips And Tricks

 

SQL*Loader loads data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts little limitation on the format of the data in the data file. You can use SQL*Loader to do the following:

  • Load data across a network if your data files are on a different system than the database.
  • Load data from multiple data files during the same load session.
  • Load data into multiple tables during the same load session.
  • Specify the character set of the data.
  • Selectively load data (you can load records based on the records’ values).
  • Manipulate the data before loading it, using SQL functions.
  • Generate unique sequential key values in specified columns.
  • Use the operating system’s file system to access the data files.
  • Load data from disk, tape, or named pipe.
  • Generate sophisticated error reports, which greatly aid troubleshooting.
  • Load arbitrarily complex object-relational data.
  • Use secondary data files for loading LOBs and collections.
  • Use conventional, direct path, or external table loads.

  1. Input Datafile contains file containing the data to be loaded.The record format can be specified in the control file with the INFILE parameter.
    cat /home/oracle/employee.txt
    
    100,Hari,MCA,5000
    200,Karthi,Technology,5500
    300,Sunil,Technology,7000
  2. Control file contains the instructions to the sqlldr utility. This tells sqlldr the location of the input file, the format of the input file, and other optional meta data information required by the sqlldr to upload the data into oracle tables.
load data
infile '/home/oracle/employee.txt'
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )

The above control file indicates the following:

  • infile – Indicates the location of the input data file
  • into table – Indicates the table name where this data should be inserted
  • fields terminated by – Indicates the delimiter that is used in the input file to separate the fields
  • ( emp_id, emp_name, dept, salary ) – Lists the name of the column names in the table into which the data should be uploaded

3. Discard file contains rejected rows those were discarded because they were filtered due to a statement in SQL*Loader control file. Data written to any database table is not written to the discard file.

4. Bad File contains the records which are rejected either by SQL Loader or by the database because of the bad formatting or data type mismatch.It will have the same name as the data file, with a .bad extension.

5. Log File  contains a detailed summary of the load, including a description of any errors that occurred during the load.

Basics execution of sqlloader :-

Create the table structure:-

SQL> create table employee(emp_id integer,emp_name varchar2(10),dept varchar2(15),salary integer,Join_date date);

Table created.

Input Data :-(Datafile)

cat /home/oracle/employee.txt

100,Hari,MCA,5000
200,Karthi,Technology,5500
300,Sunil,Technology,7000

INSERT : Default value for loading data using SQL loader.

Sqlldr control file :-

[oracle@test]  cat > sqlldr-add-records.ctl
load data
infile '/home/oracle/employee.txt'
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )

Run the sqlloader utility:-

[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-add-records.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 13 02:40:28 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 3

Table EMPLOYEE:
3 Rows successfully loaded.

Check the log file:
sqlldr-add-records.log
for more information about the load.

Check the loaded data in table 

SQL> select * from hari.employee;

EMP_ID      EMP_NAME      DEPT        SALARY       JOIN_DATE
---------- ---------- --------------- -------      ---------
100           Hari        MCA          5000
200          Karthi       Technology   5500
300           Sunil       Technology   7000

APPEND :

Input data into existing table employee :-

cat /home/oracle/newemployee.txt

400,Ram,DBA,5500
500,Siva,Developer,7000
[oracle@test]  cat > sqlldr-append-records.ctl
load data
infile '/home/oracle/newemployee.txt'
append
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )
[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-append-records.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 13 02:47:18 2018Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 2

Table EMPLOYEE:
2 Rows successfully loaded.

Check the log file:
sqlldr-append-records.log
for more information about the load.
SQL> select * from hari.employee;

EMP_ID     EMP_NAME   DEPT            SALARY    JOIN_DATE
---------- ---------- --------------- ---------- ---------
100        Hari      MCA              5000
200        Karthi    Technology       5500
300        Sunil     Technology       7000
400        Ram       DBA              5500
500        Siva      Developer        7000

5 rows selected.

TRUNCATE :

Table Structure :

SQL> select * from hari.employee;

EMP_ID     EMP_NAME   DEPT            SALARY     JOIN_DATE
---------- ---------- --------------- ---------- ---------
100        Hari       MCA             5000
200        Karthi     Technology      5500
300        Sunil      Technology      7000
400        Ram        DBA             5500
500        Siva       Developer       7000

5 rows selected.

Input Data file :-

cat /home/oracle/newemployee.txt

400,Sam,DBA,5500
500,Scott,Developer,7000

Sqlloader control file :

cat > sqlldr-truncate-records.ctl
load data
infile '/home/oracle/newemployee.txt'
truncate
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )

Sqlloader execution :-

[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-truncate-records.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Sep 14 15:22:16 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 2

Table EMPLOYEE:
2 Rows successfully loaded.

Check the log file:
sqlldr-truncate-records.log
for more information about the load.

Check the table data,

SQL> select * from hari.employee;

EMP_ID     EMP_NAME   DEPT            SALARY     JOIN_DATE
---------- ---------- --------------- ---------- ---------
400        Ram        DBA             5500
500        Siva       Developer       7000

REPLACE : 

When REPLACE is specified, the entire table is replaced, not just individual rows.It uses an implicit truncate of the table to replace existing data. It does not look at specific rows but rather removes all rows and inserts new ones, even if the new data is the same as the original data.

Table Structure :-

SQL> select * from hari.employee;

EMP_ID     EMP_NAME    DEPT         SALARY  JOIN_DATE
---------- ----------  -----------  ------- ---------
4000        Steven     ITlead        50000
5000        Brad       SystemAdmin   10000

Input data :-

[oracle@orcl:~ orcldemo] cat /home/oracle/newemployee.txt
4000,Steven,ITlead,50000
5000,Brad,SystemAdmin,10000

Control file :-

[oracle@orcl:~ orcldemo] cat sqlldr-replace-records.ctl

load data
infile '/home/oracle/newemployee.txt'
replace
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )

Sqlloader execution :-

[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-replace-records.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Sep 14 17:08:22 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 2

Table EMPLOYEE:
2 Rows successfully loaded.

Check the log file:
sqlldr-replace-records.log
for more information about the load.

Check Table data,

SQL> select * from hari.employee;

EMP_ID     EMP_NAME    DEPT         SALARY  JOIN_DATE
---------- ----------  -----------  ------- ---------
4000        Steven     ITlead        50000
5000        Brad       SystemAdmin   10000

Bad and Discard file Scenario :-

Table Structure :-

SQL> create table dept (deptno number,dname varchar(20),location varchar(20));

Table created.

Input data:-

cat sqlloader.dat

10,ACCOUNTING,PAK
30,SALES,RUSSIA
4D,OPERATIONS,USA
50,HUMAN RESOURCE,USA
60,IT,USA
70,PRODUCTION,ENG
80,QUALITY,USA

Control file :

cat sqlload.ctl
load data
infile '/home/oracle/sqlloader.dat'
badfile '/home/oracle/badrecords.bad'
discardfile '/home/oracle/dicardload.dsc'
into table dept
WHEN LOCATION!='USA'
fields terminated by ","
(DEPTNO,DNAME,LOCATION)

Run the sqlloader utility :-

[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlload.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Sep 14 14:43:30 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 7

Table DEPT:
3 Rows successfully loaded.

Check the log file:
sqlload.log
for more information about the load.

Discard file record:-

[oracle@test:~ orcldemo] cat dicardload.dsc
4D,OPERATIONS,USA
50,HUMAN RESOURCE,USA
60,IT,USA
80,QUALITY,USA

Bad file record:-

[oracle@test:~ orcldemo] cat badrecords.bad
10,ACCOUNTING,PAK
30,SALES,RUSSIA
70,PRODUCTION,ENG

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

 

 

All In One Datapump Parameters Up To Oracle 18c (A-Z)

Description:-

In this article we are covering all the datapump parameters upto 18c database

Oracle 10g Release 1

ATTACH https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-attach-parameter/

CONTENT https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-content-parameter-2/

DIRECTORY https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-directory-parameter/ 
    
DUMPFILE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-dumpfile-parameter/          
        
ESTIMATE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-estimate-parameter/

ESTIMATE_ONLY  http://www.oracledbwr.com/18c-datapump/oracle-18c-datap…e_only-parameter/


EXCLUDE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-exclude-parameter/


FLASHBACK_SCN https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-flashback_scn-parameter/  
 
FLASHBACK_TIME https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-flashback_time-parameter/  
 
FULL https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-database-level/

HELP https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-help-parameter/

INCLUDE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-include-parameter/ 
JOB_NAME https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-job_name-parameter/

NETWORK_LINK https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-network-link/
NOLOGFILE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-nologfile-parameter/

PARALLEL https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-parallel-parameter/

PARFILE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-parfile-parameter/

QUERY https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-query-parameter/

REMAP_DATAFILE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-remap_datafile-parameter/

REMAP_SCHEMA https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-remap_schema-parameter/ 

REMAP_TABLESPACE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-remap_tablespace-parameter/         
            
REUSE_DUMPFILES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-reuse_dumpfiles-parameter/

SCHEMAS https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-schema-level/

SKIP_UNUSABLE_INDEXES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-skip_unusable_indexes-parameter/

SQLFILE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-sqlfile-parameter/

STATUS https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-status-parameter/

 
TABLE_EXISTS_ACTION  https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-table_exists_action-parameter/


TABLES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-table-level/

TABLESPACES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-tablespace-level/

TRANSPORT_DATAFILES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-transport_datafiles-parameter/

TRANSPORT_TABLESPACES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-transport_tablespaces-parameter/

VERSION https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-version-parameter/

CONTINUE_CLIENT https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-continue_client-parameter/

EXIT_CLIENT https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-exit_client-parameter/

HELP https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-help-parameter/

KILL_JOB https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-kill_job-parameter/

PARALLEL https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-parallel-parameter/

START_JOB https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-start_job-parameter/

STATUS https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-status-parameter/

STOP_JOB https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-stop_job-parameter/

ADD_FILE https://www.oracledbwr.com/18c-database/oracle-18c-datapump-add_file-parameter/

REMAP_SCHEMA https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-remap_schema-parameter/

Oracle 10g Release 2

ENCRYPTION_PASSWORD https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-encryption_password-parameter/


FILESIZE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-filesize-parameter/

SAMPLE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-sample-parameter/

COMPRESSION https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-compression-parameter/

Oracle 11g Release 1


PARTITION_OPTIONS https://www.oracledbwr.com/18c-datapump/oracle-18c-datap…ptions-parameter/


REMAP_DATA http://www.oracledbwr.com/18c-datapump/oracle-18c-datap…p_data-parameter/


REMAP_TABLE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-remap_table-parameter/

ENCRYPTION https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-encryption-parameter-2/

ENCRYPTION_ALGORITHM https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-encryption_algorithm-parameter/

ENCRYPTION_MODE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-encryption_mode-parameter/

Oracle 11g Release 2

Source_edition

https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-source_edition-parameter/

Target_edition

https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-target_edition-parameter/
Oracle 12c Release 1

VIEWS_AS_TABLES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-views_as_tables-parameter/

METRICS https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-metrics-parameter/

MASTER_ONLY http://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-master_only-parameter/


KEEP_MASTER https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-keep_master-parameter/

ENCRYPTION_PWD_PROMPT https://www.oracledbwr.com/uncategorized/oracle-18c-datapump-encryption_pwd_prompt-parameter/


DISABLE_ARCHIVE_LOGGING https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-disable_archive_logging-parameter/
LOGTIME https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-logfile-parameter/


Oracle 12c Release 2

REMAP_DIRECTORY https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-remap_directory-parameter/

TRANSPORT_DATAFILES https://www.oracledbwr.com/18c-datapump/oracle-18c-datap…afiles-parameter/

LOGTIME https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-logtime-parameter/

Oracle 18c Release 1

DATABASE LINK https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-database-link/