Superdooperhero Superdooperhero - 1 year ago 120
SQL Question

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

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

Ben Ben
Answer Source

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)

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

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