Oracle Database Sql Certified Associate · Free Practice Question Medium
Question 4
View and examine the following SQL exhibit.
Identify the situations where a new transaction always starts. (Choose three)
-
A
When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session
-
B
When issuing the first Data Manipulation Language (DML) statement after a COMMIT or ROLLBACK statement was issued in the same session
-
C
When issuing a DML statement after a DML statement failed in the same session
-
D
When issuing a TRUNCATE statement after a SELECT statement was issued in the same session
-
E
When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session
-
F
When issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session
Reveal correct answers
Correct answers: A, B, D
Explanation
Correct Statements:
When issuing a TRUNCATE statement after a SELECT statement was issued in the same session
TRUNCATEis a DDL statement and causes an implicit commit.Even though it does not start a transaction, it ends any existing transaction, so the next DML will start a new transaction.
Correct in terms of triggering a new transactional state.
When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session
Again,
CREATE TABLEis DDL, and it causes an implicit commit.This ends any ongoing transaction. So, after this, the next DML would begin a new transaction.
When issuing the first Data Manipulation Language (DML) statement after a COMMIT or ROLLBACK statement was issued in the same session
After a
COMMITorROLLBACK, the next DML automatically starts a new transaction.This is the classic definition of a new transaction beginning.
Incorrect Statements:
When issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session
Both
CREATE TABLEandCREATE INDEXare DDL statements.Neither starts a transaction.
They perform implicit commits, but do not start a new transaction.
When issuing a DML statement after a DML statement failed in the same session
A failed DML doesn't end or commit a transaction.
You're still in the same transaction — Oracle lets you retry or continue within it.
So, the next DML does not necessarily start a new transaction.
When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session
CREATE TABLE AS SELECTis a DDL, so it causes an implicit commit.SELECT FOR UPDATEis not a DML statement (it only locks rows; it doesn't change data).No new transaction starts unless an actual DML follows.
Structure of a Transaction
A database transaction consists of one or more statements. Specifically, a transaction consists of one of the following:
One or more data manipulation language (DML) statements that together constitute an atomic change to the database
One data definition language (DDL) statement
A transaction has a beginning and an end.
A transaction begins when the first executable SQL statement is encountered.
An executable SQL statement is a SQL statement that generates calls to a database instance, including DML and DDL statements and the SET TRANSACTION statement.
When a transaction begins, Oracle Database assigns the transaction to an available undo data segment to record the undo entries for the new transaction. A transaction ID is not allocated until an undo segment and transaction table slot are allocated, which occurs during the first DML statement. A transaction ID is unique to a transaction and represents the undo segment number, slot, and sequence number.
A transaction can end under different circumstances.
A transaction ends when any of the following actions occurs:
A user issues a
COMMITorROLLBACKstatement without aSAVEPOINTclause.In a commit, a user explicitly or implicitly requested that the changes in the transaction be made permanent. Changes made by the transaction are permanent and visible to other users only after a transaction commits. The transaction shown in "Sample Transaction: Account Debit and Credit" ends with a commit.
A user runs a DDL command such as
CREATE,DROP,RENAME, orALTER.
The database issues an implicit COMMIT statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction.
A user exits normally from most Oracle Database utilities and tools, causing the current transaction to be implicitly committed. The commit behavior when a user disconnects is application-dependent and configurable.
A client process terminates abnormally, causing the transaction to be implicitly rolled back using metadata stored in the transaction table and the undo segment.
After one transaction ends, the next executable SQL statement automatically starts the following transaction.
A. When a CREATE TABLE statement is issued after a SELECT statement in the same session, a new transaction always starts. CREATE TABLE is a DDL statement that automatically commits the current transaction and starts a new one.
B. When the first Data Manipulation Language (DML) statement is issued after a COMMIT or ROLLBACK statement in the same session, a new transaction always starts. COMMIT and ROLLBACK statements end the current transaction, so any subsequent DML statement initiates a new transaction.
C. When a DML statement is issued after a previous DML statement failed in the same session, a new transaction does not necessarily start. The failure of a DML statement does not automatically trigger the start of a new transaction.
D. When a TRUNCATE statement is issued after a SELECT statement in the same session, a new transaction always starts. TRUNCATE is a DDL statement that cannot be rolled back, so it automatically commits the current transaction and starts a new one.
E. When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement in the same session, a new transaction does not necessarily start. Both SELECT FOR UPDATE and CREATE TABLE AS SELECT are DML statements that do not require the initiation of a new transaction.
F. When issuing a CREATE INDEX statement after a CREATE TABLE statement in the same session, a new transaction does not necessarily start. Both CREATE INDEX and CREATE TABLE are DDL statements that do not require a new transaction to be initiated.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
