Oracle8 Utilities Release 8.0 A58244-01 |
Library | Product | Contents | Index

# 7
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 will be records of successful SQL*Loader execution. However, errors can also cause log file entries. For example, errors found during parsing of the control file will appear in the log file.

This chapter describes the following log file entries:

- Header Information
- Global Information
- Table Information
- Datafile Information
- Table Load Information
- Summary Statistics

## Header Information

The Header Section contains the following entries:

- date of the run
- software version number

For example:

    SQL*Loader: Version 8.0.2.0.0 - Production on Mon Nov 26...
    Copyright (c) Oracle Corporation...

## 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 data file 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 record were loaded or only those meeting WHEN-clause criteria.
- INSERT, APPEND, or REPLACE specification
- the following column information:
  - if found in data file, the position, length, datatype, and delimiter
  - if specified, RECNUM, SEQUENCE, or CONSTANT
  - 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

## Datafile Information

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

- SQL*Loader/Oracle data records 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 met no WHEN-clause tests
- number of rows whose relevant fields were all null

For example:

    The following indexes on table EMP were processed:
    Index EMPIX was left in Direct Load State due to
    ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

    Table EMP:
    7 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.

## 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 data files, the number of records that were:
  - skipped
  - read
  - rejected
  - discarded
- beginning/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 Mon Nov 26 10:46:53 1990
    Run ended on Mon Nov 26 10:47:17 1990

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

### Oracle8 Statistics Reporting to the Log

Oracle8 statistics reporting to the log file differs between different load types:

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

#### Statistics for Loading a Single Partition

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

#### Statistics for Loading a Table

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

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

If media recovery is not enabled, the load is not logged. That is, media recovery disabled overrides the request for a logged operation.

#### New Command-line Option: `silent=partitions|all`

The command-line option, `silent=partitions`, disables output of the per-partition statistics section to the log file for direct loads of a partitioned table.

In Oracle8, the option `silent=all` includes the `partitions` flag and suppresses the per-partition statistics.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index