oracle - what statements need to be committed

Question

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

User · Accepted Answer

DML  Data Manipulation Language  commands need to be commited rolled back  Here is a list of those commands   Data Manipulation Language  DML  statements are used for managing data within schema objects  Some examples   INSERT - insert data into a table UPDATE - updates existing data within a table DELETE - deletes records from a table  the space for the records remain MERGE - UPSERT operation  insert or update  CALL - call a PL SQL or Java subprogram EXPLAIN PLAN - explain access path to data LOCK TABLE - control concurrency

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to database

Examples related to oracle

Examples related to session

Examples related to commit