[sql] oracle - what statements need to be committed?

What are the list of statements that need to be committed before further action on the table in order to avoid a lock? I am not talking about full transactions with multiple statements and transaction integrity; instead I am referring to single statements.

I know insert should be committed but truncate has an autocommit. What is the full list of statements that need to be committed?

Need to be committed (starter list):

UPDATE
INSERT
DELETE

This question is related to sql database oracle session commit

The answer is


In mechanical terms a COMMIT makes a transaction. That is, a transaction is all the activity (one or more DML statements) which occurs between two COMMIT statements (or ROLLBACK).

In Oracle a DDL statement is a transaction in its own right simply because an implicit COMMIT is issued before the statement is executed and again afterwards. TRUNCATE is a DDL command so it doesn't need an explicit commit because calling it executes an implicit commit.

From a system design perspective a transaction is a business unit of work. It might consist of a single DML statement or several of them. It doesn't matter: only full transactions require COMMIT. It literally does not make sense to issue a COMMIT unless or until we have completed a whole business unit of work.

This is a key concept. COMMITs don't just release locks. In Oracle they also release latches, such as the Interested Transaction List. This has an impact because of Oracle's read consistency model. Exceptions such as ORA-01555: SNAPSHOT TOO OLD or ORA-01002: FETCH OUT OF SEQUENCE occur because of inappropriate commits. Consequently, it is crucial for our transactions to hang onto locks for as long as they need them.


DML have to be committed or rollbacked. DDL cannot.

http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

You can switch auto-commit on and that's again only for DML. DDL are never part of transactions and therefore there is nothing like an explicit commit/rollback.

truncate is DDL and therefore commited implicitly.

Edit
I've to say sorry. Like @DCookie and @APC stated in the comments there exist sth like implicit commits for DDL. See here for a question about that on Ask Tom. This is in contrast to what I've learned and I am still a bit curious about.


And a key point - although TRUNCATE TABLE seems like a DELETE with no WHERE clause, TRUNCATE is not DML, it is DDL. DELETE requires a COMMIT, but TRUNCATE does not.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to session

What is the best way to manage a user's session in React? Spring Boot Java Config Set Session Timeout PHP Unset Session Variable How to kill all active and inactive oracle sessions for user Difference between request.getSession() and request.getSession(true) PHP - Session destroy after closing browser Get Current Session Value in JavaScript? Invalidating JSON Web Tokens How to fix org.hibernate.LazyInitializationException - could not initialize proxy - no Session How can I get session id in php and show it?

Examples related to commit

git: updates were rejected because the remote contains work that you do not have locally How to add multiple files to Git at the same time Why Git is not allowing me to commit even after configuration? Git commit in terminal opens VIM, but can't get back to terminal How to configure Git post commit hook GIT commit as different user without email / or only email Editing the git commit message in GitHub How to amend a commit without changing commit message (reusing the previous one)? Temporarily switch working copy to a specific Git commit git - Your branch is ahead of 'origin/master' by 1 commit