SQL Loader bad file vs Discard File: Difference and Examples

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.
DISCARD file is to store the records that are neither inserted into table nor rejected as bad. This is an optional parameter with SQL Loader and by default name would be .dsc.
As the name indicates these records are discarded by the SQL Loader because they were filtered out with the record selection criteria mentioned in the control file commands. This file creates only when needed. You can specify the maximum number of discard records that can accept by the discard file by using DISCARDMAX parameter.
First preference goes to discard file even though there is a bad record. You cans see the example below.
Example:
My table description is
SQL>  desc dept
 Name              Null?    Type
 ----------------- -------- ------------
 DEPTNO                     NUMBER(2)
 DNAME                      VARCHAR2(14)
 LOC                        VARCHAR2(13)

Here is my data file which has 2 bad records marked in red color. Those records will not match the data type in the table.
$ cat sqlload.dat
10,ACCOUNTING,NEW YORK
2D,RESEARCH,DALLAS
30,SALES,CHICAGO
4D,OPERATIONS,BOSTON
50,HUMAN RESOURCE,BOSTON
60,IT,BOSTON
70,PRODUCTION,DALLAS
80,QUALITY,BOSTON

My control file is as follows. In my control file I have mentioned condition to insert the records with location not equal to BOSTON. So those filtered records will go to the discard file.
cat sqlload.ctl
load data
infile '/home/oracle/st/sqlload.dat'
badfile '/home/oracle/st/badrec.bad'
discardfile '/home/oracle/st/dicardload.dsc'
into table dept
WHEN LOC!='BOSTON'
fields terminated by ","
(DEPTNO,DNAME,LOC)

Executing the SQL Loader command
$ sqlldr sthomas/tiger control=/home/oracle/st/sqlload.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Tue May 28 11:36:13 2013

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

Commit point reached - logical record count 8

Now let’s check the content of the discard file and bad file. The first bad record marked to the discard file because of the filtration. As I said earlier the preference goes to the filtration. All the records with location BOSTON got filtered to the discard file.
$ cat dicardload.dsc
4D,OPERATIONS,BOSTON
50,HUMAN RESOURCE,BOSTON
60,IT,BOSTON
80,QUALITY,BOSTON

$ cat badrec.bad
2D,RESEARCH,DALLAS

I think it helped you!!!
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google