Oracle SQL Loader - How to not display "Commit point reached - logical record count" counts

Question

I'm loading big files via Oracle SQL Loader over vpn from home, and they're taking a lot of time. They were a lot faster to load when I loaded them from work. The files I'm loading are on my work server already.

So my thinking is that the slow down is because of the "Commit point reached - logical record count" that is printed for each row. Must be slow due to them having to be sent over the network. I googled but can't find any way to print less of them. Tried adding rows=5000 as a parameter, but I still get the prints for each row.

How can I print less of the "Commit point reached - logical record count" counts?

Thanks

Solution

You can use the keyword silent, which is available in the options clause. You can set the following things to be silent:

• HEADER - Suppresses the SQL*Loader header messages that normally appear on the screen. Header messages still appear in the log file.
• FEEDBACK - Suppresses the "commit point reached" feedback messages that normally appear on the screen.
• ERRORS - Suppresses the data error messages in the log file that occur when a record generates an Oracle error that causes it to be
  written to the bad file. A count of rejected records still appears.
• DISCARDS - Suppresses the messages in the log file for each record written to the discard file.
• PARTITIONS - Disables writing the per-partition statistics to the log file during a direct load of a partitioned table.
• ALL - Implements all of the suppression values: HEADER, FEEDBACK, ERRORS, DISCARDS, and PARTITIONS.

You would want to suppress feedback.

You can either use on the command line, for instance:

sqlldr schema/pw@db silent=(feedback, header)

Or in the options clause of the control file, for instance:

options (bindsize=100000, silent=(feedback, errors) )

Comments

[Superdooperhero]

Thanks, no more display. Sadly it's still taking the same time to load the data :-(

[Ben]

If you don't have any indexes in your table and you don't mind doing a direct path load try using the direct=true option, which should help as would increasing the commit interval (far more than using stdout). Up the commit interval to 20k and it will go quicker.

[Barbaros Özhan]

so far so good(+1) thanks, but I think the statement should be without parentheses such as sqlldr schema/pw@db silent=feedback, header.