Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Name

SET COPYCOMMIT

Synopsis

The COPYCOMMIT setting controls how often SQL*Plus commits during execution of a COPY command.

Syntax

SET COPYC[OMMIT] batch_count

Parameters

SET COPYC[OMMIT]

Is the command, which may be abbreviated SET COPYC.

batch_count

Is the maximum number of uncommitted batches you want to allow during a copy operation. After this many batches are sent to the server, SQL*Plus commits the changes and resets the counter before sending another batch. The default value is 0, which means that SQL*Plus commits changes only when the COPY command is finished. The maximum value for this setting is 5000.

Examples

Normally, when you execute a COPY command, SQL*Plus copies all the rows from the source table to the destination table, then commits those changes. This can make for a rather large transaction if you are copying a large number of records, and your rollback segments may not be big enough to accommodate it. You can use SET COPYCOMMIT to have SQL*Plus periodically commit the changes, thus reducing the transaction size.

The COPYCOMMIT setting works in conjunction with the ARRAYSIZE setting. The ARRAYSIZE setting controls the number of rows in a batch. The COPYCOMMIT setting controls how many batches are copied before committing. The number of rows copied before each commit is equal to ARRAYSIZE * COPYCOMMIT. Take a look at this example:

SET ARRAYSIZE 15
SET COPYCOMMIT 10
COPY TO jonathan/secret@jonathan
CREATE employee_copy
USING SELECT * FROM employee;

Because the ARRAYSIZE is 15 ...