We know that Oracle performs Database Change operations in transaction units. So when will Oracle submit? For DML statements, Oracle does not automatically commit transactions until there is a commit or rollback command to process the changes in the database. For DDL and DCl, Oracle will submit immediately. That is to say, once these two types of statements are executed, they will be reflected to the database. Another situation is, the previously executed DML is not submitted manually. After the DDL or DCL is executed, Oracle also submits the DML changes to the database.
Oralce transactions start with the first DML statement and end with the following situations:
- An explicit commit or rollback command
- A ddl or DCL statement
- The user exits SQL * Plus or SQL * plussheet or iSQL * Plus.
- System Crash
The advantages of this commit and rollback mechanisms are:
- Ensures Data Consistency
- You can preview the data before the data changes permanently.
- You can submit a logical group of operations to the database. We often need to ensure that the data in table 1 and table 2 is consistent, either inserted at the same time or not inserted at the same time. This commit and rollback mechanism is very convenient.
In this way, we need to make such an impression in our mind that Oracle will perform implicit commit operations when we execute DCL or DDL, or normally exit SQL * Plus related tools, when the system crashes or the SQL * Plus tool unexpectedly exits, data is automatically rolled back and rolled back.
It should also be noted that as long as the transaction is not completed, the data you operate on is locked. Other users except you cannot find the changes you have made, it is also impossible to update or delete the data. If other users attempt to operate the data, they will be forced to wait. In this case, the machine will often crash until you submit or roll back the relevant data.
In Oracle, we can also perform statement-level rollback. In this case, we need to define the savepoint rollback point.