All about Database Administration, Tips & Tricks

From 12c Onwards, Oracle has made some important changes to SQLloader and enhanced a lot, one of the feature was express loading with minimal configuration.

You will just need to create a data file with same name as Table name and thats it no control file or other parameters required.

It just default load those data into the table. Further, there are lot more new options for sqlldr, one must see if they are using this tool in their application batch processing.

For example, Multi threading, Degree of Parallelism, DNFS support, external table via DNFS etc. Read More here:- http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm

Let's see the express mode feature, Create a sample table

	SQL> create table EMPLOYEE (employee_id number primary key, employee_name varchar2(50)) tablespace users;

	Table created.

Create the data file to load into table, (Note the file name is also as like as table name, this is must)

	SQL> !vi EMPLOYEE.dat

	SQL> !cat EMPLOYEE.dat
	1,XXXXXXXXXXX
	2,YYYYYYYYYYY
	3,ZZZZZZZZZZZ
	4,CCCCCCCCCCC
	5,DDDDDDDDDDD
	6,FFFFFFFFFFF
	7,GGGGGGGGGGG
	8,HHHHHHHHHHH
	9,OOOOOOOOOOO
	10,JJJJJJJJJJ

Just kick off the sqlldr, As you see I have not created any control file or any commands provided, just a table name to load, this is what minimal configuration is.

	[oracle@Geek DBA12c ~]$ sqlldr test/test@pdb12c TABLE=EMPLOYEE

	SQL*Loader: Release 12.1.0.1.0 - Production on Mon Sep 16 21:01:11 2013

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

	Express Mode Load, Table: EMPLOYEE
	Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

	Table EMPLOYEE:
	  10 Rows successfully loaded.

	Check the log files:
	  EMPLOYEE.log
	  EMPLOYEE_%p.log_xt
	for more information about the load.

Lets check the logs

	[oracle@Geek DBA12c ~]$ ls -ltr
	total 88
	-rw-r--r--. 1 oracle oinstall   140 Sep 16 20:58 EMPLOYEE.dat
	-rw-r--r--. 1 oracle oinstall  1044 Sep 16 21:01 EMPLOYEE_2288.log_xt
	-rw-r--r--. 1 oracle oinstall  2355 Sep 16 21:01 EMPLOYEE.log
	[oracle@Geek DBA12c ~]$

My Table is sucessfully loaded, Also if you look at log ,

	a) Express Mode is used
	b) Parallel DML is automatically enabled
	c) Control file like file automatically created and used.

	[oracle@Geek DBA12c ~]$ cat EMPLOYEE.log

	SQL*Loader: Release 12.1.0.1.0 - Production on Mon Sep 16 21:01:11 2013

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

	Express Mode Load, Table: EMPLOYEE
	Data File:      EMPLOYEE.dat
	  Bad File:     EMPLOYEE_%p.bad
	  Discard File:  none specified

	 (Allow all discards)

	Number to load: ALL
	Number to skip: 0
	Errors allowed: 50
	Continuation:    none specified
	Path used:      External Table

	Table EMPLOYEE, loaded from every logical record.
	Insert option in effect for this table: APPEND

	   Column Name                  Position   Len  Term Encl Datatype
	------------------------------ ---------- ----- ---- ---- ---------------------
	EMPLOYEE_ID                         FIRST     *   ,       CHARACTER
	EMPLOYEE_NAME                        NEXT     *   ,       CHARACTER

	Generated control file for possible reuse:
	OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
	LOAD DATA
	INFILE 'EMPLOYEE'
	APPEND
	INTO TABLE EMPLOYEE
	FIELDS TERMINATED BY ","
	(
	  EMPLOYEE_ID,
	  EMPLOYEE_NAME
	)
	End of generated control file for possible reuse.

	created temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for path /home/oracle

	enable parallel DML: ALTER SESSION ENABLE PARALLEL DML

	creating external table "SYS_SQLLDR_X_EXT_EMPLOYEE"

	CREATE TABLE "SYS_SQLLDR_X_EXT_EMPLOYEE"
	(
	  "EMPLOYEE_ID" NUMBER,
	  "EMPLOYEE_NAME" VARCHAR2(50)
	)
	ORGANIZATION external
	(
	  TYPE oracle_loader
	  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
	  ACCESS PARAMETERS
	  (
	    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
	    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'EMPLOYEE_%p.bad'
	    LOGFILE 'EMPLOYEE_%p.log_xt'
	    READSIZE 1048576
	    FIELDS TERMINATED BY "," LRTRIM
	    REJECT ROWS WITH ALL NULL FIELDS
	    (
	      "EMPLOYEE_ID" CHAR(255),
	      "EMPLOYEE_NAME" CHAR(255)
	    )
	  )
	  location
	  (
	    'EMPLOYEE.dat'
	  )
	)REJECT LIMIT UNLIMITED

	executing INSERT statement to load database table EMPLOYEE

	INSERT /*+ append parallel(auto) */ INTO EMPLOYEE
	(
	  EMPLOYEE_ID,
	  EMPLOYEE_NAME
	)
	SELECT
	  "EMPLOYEE_ID",
	  "EMPLOYEE_NAME"
	FROM "SYS_SQLLDR_X_EXT_EMPLOYEE"

	dropping external table "SYS_SQLLDR_X_EXT_EMPLOYEE"

	Table EMPLOYEE:
	  10 Rows successfully loaded.

	Run began on Mon Sep 16 21:01:11 2013
	Run ended on Mon Sep 16 21:01:12 2013

	Elapsed time was:     00:00:01.69
	CPU time was:         00:00:00.01

	[oracle@Geek DBA12c ~]$ cat EMPLOYEE_2288.log_xt


	 LOG file opened at 09/16/13 21:01:11

	Field Definitions for table SYS_SQLLDR_X_EXT_EMPLOYEE
	  Record format DELIMITED BY NEWLINE
	  Data in file has same endianness as the platform
	  Reject rows with all null fields

	  Fields in Data Source:

	    EMPLOYEE_ID                     CHAR (255)
	      Terminated by ","
	      Trim whitespace from left and right
	    EMPLOYEE_NAME                   CHAR (255)
	      Terminated by ","
	      Trim whitespace from left and right


	 LOG file opened at 09/16/13 21:01:12

	KUP-05004:   Warning: Intra source concurrency disabled because parallel select was not requested.

	Field Definitions for table SYS_SQLLDR_X_EXT_EMPLOYEE
	  Record format DELIMITED BY NEWLINE
	  Data in file has same endianness as the platform
	  Reject rows with all null fields

	  Fields in Data Source:

	    EMPLOYEE_ID                     CHAR (255)
	      Terminated by ","
	      Trim whitespace from left and right
	    EMPLOYEE_NAME                   CHAR (255)
	      Terminated by ","
	      Trim whitespace from left and right
	[oracle@Geek DBA12c ~]$

Hope this helps.

-Thanks

Geek DBA