8
SQL*Loader Log File Reference

When SQL*Loader begins execution, it creates a log file. The log file contains a detailed summary of the load.

Most of the log file entries are records of successful SQL*Loader execution. However, errors can also cause log file entries. For example, errors found during parsing of the control file appear in the log file.

This chapter describes the following sections of a SQL*Loader log file:

• Header Information
• Global Information
• Table Information
• Datafile Information
• Table Load Information
• Summary Statistics
• Additional Summary Statistics for Direct Path Loads and Multithreading
• Log File Created When EXTERNAL_TABLE=GENERATE_ONLY

Header Information

The Header Section contains the following entries:

• Date of the run
• Software version number

For example:

SQL*Loader: Release 9.2.0.1.0 - Production on Wed Feb 27 11:07:28 2002

(c) Copyright 2002 Oracle Corporation.  All rights reserved.

Global Information

The Global Information Section contains the following entries:

• Names of all input/output files
• Echo of command-line arguments
• Continuation character specification

If the data is in the control file, then the datafile is shown as "*".

For example:

Control File:     LOAD.CTL
Data File:        LOAD.DAT
  Bad File:       LOAD.BAD
  Discard File:   LOAD.DSC

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 65536 bytes
Continuation:   1:1 = '*', in current physical record
Path used:      Conventional

Table Information

The Table Information Section provides the following entries for each table loaded:

• Table name
• Load conditions, if any. That is, whether all records were loaded or only those meeting criteria specified in the WHEN clause.
• INSERT, APPEND, or REPLACE specification
• The following column information:
  • Column name
  • If found in a datafile, the position, length, delimiter, and datatype. See Column Information for a description of these columns.
  • If specified, RECNUM, SEQUENCE, CONSTANT, or EXPRESSION
  • If specified, DEFAULTIF or NULLIF

For example:

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

   Column Name                Position   Len  Term Encl Datatype
--------------------------------------   ---  ---- ---- ---------
   empno                           1:4     4            CHARACTER
   ename                          6:15    10            CHARACTER
   job                           17:25     9            CHARACTER
   mgr                           27:30     4            CHARACTER
   sal                           32:39     8            CHARACTER
   comm                          41:48     8            CHARACTER
   deptno                        50:51     2            CHARACTER

Column empno is NULL if empno = BLANKS
Column mgr is NULL if mgr = BLANKS
Column sal is NULL if sal = BLANKS
Column comm is NULL if comm = BLANKS
Column deptno is NULL if deptno = BLANKS

Column Information

This section contains a more detailed description of the column information that is provided in the Table Information Section of the SQL*Loader log file.

Position

The following are the possibilities for the Position column:

• If a position is specified, the position values are in bytes, starting with byte position 1, regardless of whether byte-length semantics or character-length semantics are used.
• If both a start and end position are specified, they are separated by a colon.
• If only a start position is specified, then only that position is displayed.
• If no start or end position is specified, then FIRST is displayed for the first field and NEXT is displayed for other fields.
• If the start position is derived from other information, then DERIVED is displayed.

Length

The length, in bytes, is displayed under the heading Len. It gives the maximum size of the field, including the size of any embedded length fields. The size will be different with byte-length semantics versus character-length semantics. For example, for VARCHAR (2,10) with byte-length semantics, the length is 2 (the size of the length field) plus 10 (maximum size of the field itself), which equals 12 bytes. For VARCHAR (2,10) with character-length semantics, the length is calculated using the maximum size, in bytes, of a character in the datafile character set.

For fields that do not have a specified maximum length, an asterisk (*) is written in the Length column.

Delimiter

The delimiters are displayed under the headings, Term (for terminated by) and Encl (for enclosed by). If the delimiter is optional, it is preceded by O and is displayed within parentheses.

Datatype

The datatype is displayed as specified in the control file.

If the SQL*Loader control file contains any directives for loading datetime and interval datatypes, then the log file contains the parameter DATE, DATETIME, or INTERVAL under the Datatype heading. If applicable, the parameter DATE, DATETIME, or INTERVAL is followed by the corresponding mask. For example:

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

   Column Name                Position   Len  Term Encl Datatype
--------------------------------------   ---  ---- ---- ---------
   col1                           NEXT   *              DATETIME HH.MI.SSXFF AM

Datafile Information

The Datafile Information Section appears only for datafiles with data errors, and provides the following entries:

• SQL*Loader and Oracle data record errors
• Records discarded

For example:

Record 2: Rejected - Error on table EMP.
ORA-00001:  unique constraint <name> violated
Record 8: Rejected - Error on table emp, column deptno.
ORA-01722:  invalid number
Record 3: Rejected - Error on table proj, column projno.
ORA-01722:  invalid number

Table Load Information

The Table Load Information Section provides the following entries for each table that was loaded:

• Number of rows loaded
• Number of rows that qualified for loading but were rejected due to data errors
• Number of rows that were discarded because they did not meet the specified criteria for the WHEN clause
• Number of rows whose relevant fields were all null
• Date cache statistics, if applicable

For example:

Table EMP:
25000 Rows successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Date Cache:
 Max Size: 2000
 Entries:  1000
 Hits:     11000
 Misses:       0

Summary Statistics

The Summary Statistics Section displays the following data:

• Amount of space used:
  • For bind array (what was actually used, based on what was specified by BINDSIZE)
  • For other overhead (always required, independent of BINDSIZE)
• Cumulative load statistics. That is, for all datafiles, the number of records that were:
  • Skipped
  • Read
  • Rejected
  • Discarded
• Beginning and ending time of run
• Total elapsed time
• Total CPU time (includes all file I/O but may not include background Oracle CPU time)

For example:

Space allocated for bind array:              65336 bytes (64 rows)
Space allocated for memory less bind array:  6470 bytes

Total logical records skipped:          0
Total logical records read:             7
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Wed Feb 27 10:46:53 1990
Run ended on Wed Feb 27 10:47:17 1990

Elapsed time was:     00:00:15.62
CPU time was:         00:00:07.76

Oracle Statistics That Are Logged

The statistics that are reported to the log file vary, depending on the load type.

• For conventional loads and direct loads of a nonpartitioned table, statistics reporting is unchanged from Oracle7.
• For direct loads of a partitioned table, a per-partition statistics section is provided after the table-level statistics section.
• For a single-partition load, the partition name will be included in the table-level statistics section.

Information About Single-Partition Loads

The following information is logged when a single partition is loaded:

• The table column description includes the partition name.
• Error messages include the partition name.
• Statistics listings include the partition name.

Statistics for Loading a Table

The following statistics are logged when a table is loaded:

• Direct path load of a partitioned table reports per-partition statistics.
• Conventional path load cannot report per-partition statistics.
• For loading a nonpartitioned table, statistics are unchanged from Oracle7.

For conventional loads and direct loads of a nonpartitioned table, statistics reporting is unchanged from Oracle7.

If you request logging, but media recovery is not enabled, the load is not logged.

Additional Summary Statistics for Direct Path Loads and Multithreading

For direct path loads, the log contains the following additional data (the numbers in your log file will be different):

Column array rows:      20000
Stream buffer bytes:   256000

See Specifying the Number of Column Array Rows and Size of Stream Buffers for information about the origin of these statistics.

Direct path loads on multiple-CPU systems have the option of using multithreading. If multithreading is enabled (the default behavior), the following additional statistics are logged (the numbers in your log will be different):

Total stream buffers loaded by SQL*Loader main thread:   102
Total stream buffers loaded by SQL*Loader load thread:   200

See Optimizing Direct Path Loads on Multiple-CPU Systems for more information about multithreading.

Log File Created When EXTERNAL_TABLE=GENERATE_ONLY

When you use the external tables feature, you can place all of the SQL commands needed to do the load, as described in the control file, in the SQL*Loader log file. To do this, set the EXTERNAL_TABLE parameter to GENERATE_ONLY. The actual load can be done later without the use of SQL*Loader by executing these statements in SQL*Plus.

To generate an example of the log file created when using EXTERNAL_TABLE=GENERATE_ONLY, execute the following command for case study 1 (Case Study 1: Loading Variable-Length Data):

sqlldr scott/tiger ulcase1 EXTERNAL_TABLE=GENERATE_ONLY

The resulting log file looks as follows:

SQL*Loader: Release 9.2.0.1.0 - Production on Wed Feb 27 11:07:28 2002

(c) Copyright 2002 Oracle Corporation.  All rights reserved.

Control File:   ulcase1.ctl
Data File:      ulcase1.ctl
  Bad File:     ulcase1.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 DEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,  O(") CHARACTER            
DNAME                                NEXT     *   ,  O(") CHARACTER            
LOC                                  NEXT     *   ,  O(") CHARACTER            



CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 
'/private/adestore/krich/.ade/view_storage/krich_dev/rdbms/demo'


CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT" 
(
  DEPTNO NUMBER(2),
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13)
)
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':'ulcase1.bad'
    LOGFILE 'ulcase1.log_xt'
    READSIZE 1048576
    SKIP 20
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      DEPTNO CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      DNAME CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      LOC CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location 
  (
    'ulcase1.ctl'
  )
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO DEPT 
(
  DEPTNO,
  DNAME,
  LOC
)
SELECT 
  DEPTNO,
  DNAME,
  LOC
FROM "SYS_SQLLDR_X_EXT_DEPT"


statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_DEPT"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000


Run began on Wed Feb 27 11:07:28 2002
Run ended on Wed Feb 27 11:07:34 2002

Elapsed time was:     00:00:06.13
CPU time was:         00:00:00.20