SQL*Loader DISCARDMAX Parameter

This parameter specifies the maximum number of discard records can be allowed during a particular data load. This is a number parameter and if you are not specifying any number for this argument then it will consider all discard records. Otherwise it will exit the data load once it reaches the limit. See an example below
Here is my data file

$ cat *dat
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
40,OPERATIONS,BOSTON
30,SALES,CHICAGO
50,HUMAN RESOURCE,BOSTON
60,IT,BOSTON
70,PRODUCTION,DALLAS
80,QUALITY,BOSTON
Here is my control file
$ cat *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)

I am specifying the DISCARDMAX parameter value as 1. This command will load the records in the flat file upto the second discard record.
$ sqlldr sthomas/tiger control=/home/oracle/st/sqlload.ctl DISCARDMAX=1

SQL*Loader: Release 11.2.0.3.0 - Production on Thu Jun 6 11:26:17 2013

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

Commit point reached - logical record count 5

Content for the discard file.
$ cat *dsc
40,OPERATIONS,BOSTON
50,HUMAN RESOURCE,BOSTON

Here is the records loaded to the table
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

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