Oracle Database Sql Certified Associate · Free Practice Question Medium
Question 29
View and examine the following available responses.
Identify the situations where a transaction completes. (Choose three)
-
A
When a TRUNCATE statement is executed after the pending transaction
-
B
When a DELETE statement is executed
-
C
When a PL/SQL anonymous block is executed
-
D
When a ROLLBACK command is executed
-
E
When a data definition language (DDL) statement is executed
Reveal correct answers
Correct answers: A, D, E
Explanation
The three situations where a transaction completes are:
When a ROLLBACK command is executed – This ends the current transaction and undoes any changes made during it.
When a data definition language (DDL) statement is executed – DDL statements like
CREATE,ALTER, andDROPtrigger an implicit commit before and after execution, completing the transaction.When a TRUNCATE statement is executed after the pending transaction –
TRUNCATEis a DDL operation and also causes an implicit commit, completing the transaction.
The other two options don’t necessarily complete a transaction:
A PL/SQL anonymous block may contain transactional statements, but it doesn’t automatically commit or rollback unless explicitly coded to do so.
A DELETE statement is a DML operation and doesn’t complete a transaction unless followed by a
COMMITorROLLBACK.
Transaction Management
A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit. The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).
A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued.
Statement Execution and Transaction Control
A SQL statement that runs successfully is different from a committed transaction. Executing successfully means that a single statement was:
• Parsed
• Found to be a valid SQL construction
• Run without error as an atomic unit. For example, all rows of a multirow update are changed.
However, until the transaction that contains the statement is committed, the transaction can be rolled back, and all of the changes of the statement can be undone. A statement, rather than a transaction, runs successfully.
Committing means that a user has explicitly or implicitly requested that the changes in the transaction be made permanent.
• An explicit request occurs when the user issues a COMMIT statement.
• An implicit request occurs after normal termination of an application or completion of a data definition language (DDL) operation.
The changes made by the SQL statement(s) of a transaction become permanent and visible to other users only after that transaction commits. Queries that are issued after the transaction commits will see the committed changes.
The Data Definition Language (DDL) commands in Oracle SQL include:
CREATE: creates objects in the database, such as as tables, views, and functions.
ALTER: changes or alters objects in the database, such as tables and views. Note: this includes all ALTER statements except ALTER SESSION and ALTER SYSTEM.
DROP: drops, or deletes, objects in the database.
TRUNCATE: removes all data from a table.
RENAME: changes the name of an object in the database.
COMMENT: adds comments for an object to the data dictionary.
GRANT: give privileges to a user or role in the database.
REVOKE: remove certain privileges from a user or role in the database.
ANALYZE: analyses information on a table, index, or cluster.
AUDIT: Track the occurrence of SQL statements in user sessions or on specific schema objects.
NOAUDIT: Disables auditing set up by the AUDIT command.
ASSOCIATE STATISTICS: Associate a statistics type with columns, functions, and other objects.
DISASSOCIATE STATISTICS: Remove the statistics association set up by ASSOCIATE STATISTICS.
FLASHBACK: Restore an earlier version of a table.
PURGE: Remove a table or index from the recycle bin.
DDL commands cannot be rolled back, as they include a COMMIT as part of their execution.
Explicit Transaction Control:
A transaction is completed if the PL/SQL block contains a COMMIT or ROLLBACK statement.
Example:
- sqlCode kopierenBEGIN INSERT INTO employees VALUES (1, 'John'); COMMIT; -- Transaction completes hereEND;
No Transaction Control:
If the block doesn't contain COMMIT or ROLLBACK, the transaction remains open until the session explicitly commits or rolls back the changes.
Example:
- sqlCode kopierenBEGIN INSERT INTO employees VALUES (2, 'Jane'); -- No COMMIT or ROLLBACK, so the transaction is still pendingEND;
Exceptions:
If an exception occurs and is not handled, the transaction is not committed and may be implicitly rolled back, depending on the database.
A. When a TRUNCATE statement is executed after the pending transaction, it is used to remove all rows from a table quickly and efficiently. The transaction completes when the TRUNCATE statement is executed, and the table is truncated, removing all data without generating undo logs.
B. When a DELETE statement is executed, it is considered a data manipulation language (DML) operation that modifies data in a table. The transaction completes when the DELETE operation is successfully executed, and the changes are committed to the database.
C.
The transaction's completion depends on whether the block contains explicit COMMIT or ROLLBACK statements.
D. When a ROLLBACK command is executed, it is used to undo all changes made in the current transaction and restore the database to its state before the transaction began. The transaction completes when the ROLLBACK command is executed, and the changes are reverted.
E. When a data definition language (DDL) statement is executed, it is used to define, modify, or drop database objects such as tables, views, or indexes. The transaction completes when the DDL statement is successfully executed, and the changes are committed to the database.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
