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 ...
Get Oracle SQL*Plus: The Definitive Guide, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.