Name
SET AUTOCOMMIT
Synopsis
The AUTOCOMMIT setting controls whether SQL*Plus automatically commits changes you make to the database, and it controls how often those changes are committed.
Syntax
SET AUTO[COMMIT] {OFF | ON | IMMEDIATE | statement_count
}
Parameters
- SET AUTO[COMMIT]
Is the command, which may be abbreviated SET AUTO.
- OFF
Turns off autocommit and requires you to commit (or roll back) changes manually. This is the default setting.
- ON
Causes SQL*Plus to issue a COMMIT after each successful SQL statement or PL/SQL block you execute.
- IMMEDIATE
Has the same effect as ON.
- statement_count
Causes SQL*Plus to issue a COMMIT after successfully executing the specified number of SQL statements or PL/SQL blocks. This value may range from 1 to 2,000,000.
Examples
When you set autocommit to occur after a specified number of successful SQL statements, be aware that manually executing a COMMIT, a ROLLBACK, or another SET AUTOCOMMIT command will cause the counter to be reset back to zero. Take a look at the following example:
SET AUTOCOMMIT 5 DELETE FROM project_hours WHERE employee_id = 101 AND project_id = 1001; DELETE FROM project_hours WHERE employee_id = 102 AND project_id = 1001; COMMIT; DELETE FROM project_hours WHERE employee_id = 103 AND project_id = 1001; DELETE FROM project_hours WHERE employee_id = 104 AND project_id = 1001; DELETE FROM project_hours WHERE employee_id = 105 AND project_id = 1001; DELETE FROM project_hours WHERE employee_id = 106 AND project_id = 1001; DELETE FROM project_hours ...
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.